Category Archives: SQL Scripts

Example of Deadlock | SQL Server Deadlock Script

IS there a way I can stimulate a deadlock in SQL Server, so that I can use that as an example of deadlock Let’s take an example, where we will result / cause a dead lock to SQL Serve? To stimulate the Deadlock, let’s create two tables which we are going to use as an [...]

SQL Script to find Current Running Jobs and elapsed time

I do get a call from developers to check weather a any job is running on the server or not ? Mostly on Sunday morning they want to check that all data import / exports jobs finishes on time. So they wanted me to know what all the jobs are currently executing. We can find [...]

SQL SCRIPT to identify Bottleneck / Diagnostic Information Queries

I was thinking for building a consolidated Script to to identify performance bottlenecks, where I wanted to looks at following information : SQL and OS Version information for current instance Hardware information System Manufacturer and model number and processor description SQL Server configuration File Names and Paths for TempDB and all user databases Recovery model, [...]

SQL Script to know Temporary Tables and Their Size

Check my previous Post "How to Optimize tempdb ? ", where I have already answered What is Stored in tempdb ? A more about tempdb Internals How to Monitor tempdb How to Optimize tempdb tempdb Space Allocation What sort of problem you may have with tempdb How to Monitoring tempdb Space Tips for Optimizing tempdb [...]

SQL Script to get Client Connection Details

Being a DBA, to optimize a performance, at times, you might want to know How many connections are currently established on SQL Server Instance? From where SQL Server connection are being made? What is client name  / IP Address from SQL Server connection are being made. What Login name is being used to make a [...]

Script to Identify worst Performing Queries

Being a DBA, to optimize a performance, a DBA need to know What are most most frequently executed queries on your system. What are the Queries / Statements which which makes system real busy. What are the top worst performed queries How much IO is being caused by a particular query What is the CPU [...]

SQL Script to get Server CPU Information

At times, DBA need to know, How many physical CPU has been installed How many Logical CPU (Cores) are running Type of CPU Processor Model and processing power of CPU This information ideally should in Inventory sheet or should be provided by System support executive but at times these resources are not available on the [...]