Check if PowerShell is Installed on SQL Server

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following: Is PowerShell installed on your local SQL Server? Is it enabled for script execution? What is the version of PowerShell installed on your machine? You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both... Read more

Server side lightweight SQL Server tracing

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by... Read more

T-SQL Script to find High CPU Queries

There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to: Identify the query that is causing high CPU Identify the total time taken by query to execute that query Find out when was the last time the query was executed Obtain Query execution plan for the offending query There are many scripts available on the internet and many-a-times that... Read more

How to restore damaged pages from a backup

Are your database pages corrupt or damaged? Now you can restore damaged pages without impacting the availability of the database. SQL Server provides an extension to the RESTORE command that can help restore damaged pages from the backups. (Note: Only the portion that is being restored will be unavailable) Important things to Keep in Mind: You cannot restore log pages You cannot restore GAM, SGAM and PFS pages. You cannot restore Page 0 of all data files (Page 0... Read more

Why do Queries behave differently in production

You may have experienced something similar where the query or a stored procedure performs bad when deployed in the production and the same query / stored procedure works perfectly well in the test or development environment. Behind the scenes (Query Internals) It is possible for the query or the stored procedure to behave differently in lower environments than Production if you have not cleaned up the query execution plan cache when performing tests. Internally,... Read more