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 Update Statistics

Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and optimal plan. This script allows you to report and update outdated statistics. In reporting mode, this script provides the following information: 1. Name of the Database 2. Table Name 3. Index... Read more

T-SQL Script to Attach and Detach Databases

DBAs and Developers at times may need to detach and attach the databases for various reasons including the following: 1. Moving files to other drives 2. Server migration 2. Upgrades, 3. system maintenance, etc. This script takes away the headache of remembering the filename or folder location of every single data or log file for every single user databases on your server. It allows you to generate script to detach and attach one or all databases avoiding the need... Read more

T-SQL Script to Check Database Compatibility

Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database... Read more

T-SQL Script to Obtain Database Growth Information

DBA’s and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information: 1. Local Server Name 2. Database Name 3. Logical File Name 4. Initial File Size 5. File Growth Value/Percentage 6. Any Growth Restrictions Please run the SPROC without any parameters to obtain help on how to execute this... Read more

T-SQL Query to find CPU and NUMA Nodes

I’m often asked on how to identify the number of CPU’s (physical) and Numa Nodes (logical CPU’s) from SQL Server. Here’s a quick and easy way to obtain such information. Run this query on SQL Server Instance you want to get data from SELECT (cpu_count / hyperthread_ratio) AS Number_of_PhysicalCPUs, CPU_Count AS Number_of_LogicalCPUs FROM sys.dm_os_sys_info 1234 SELECT   (cpu_count / hyperthread_ratio) AS... Read more