My DBA Toolkit |
|
My DBA Toolkit
A little about me:
I do not have a computer science degree and I am not fresh out of college. I am not a “SQL MVP” or “MCM.” Just a guy in the trenches working in data. Being a DBA is a second career for me. I was a musician and music teacher for 12 years. In 2010, I was telling a friend of mine, who is a MicroStrategy developer, how I was burned-out and wanted to change careers. He told me, “Learn SQL and you will always have a job." I had no idea what SQL was. I bought a SQL Server training series and studied for about 9 months and took the SQL Server 2008 Development test. Getting that certification helped propel me into a job as a SQL Server DBA in Dallas, TX. The senior DBA that hired me was a SQL MVP and MCM. I tried to soak up as much knowledge as I could from him. I never in a million years thought I would be doing this. It is quite rewarding. I love the process of trying to solve a problem.
When evaluating whether a DBA job req is something I would be interested in pursuing, one of the questions I ask is what monitoring solution the prospective employer has in place. Being able to evaluate the performance of your SQL Servers from a historical perspective is invaluable. Monitoring tools are only the start and cannot be solely relied upon to solve your technical issues. Having a set of “go-to” scripts is necessary for any DBA regardless of how veteran you are in the field.
Over the years, I have collected my set of “go-to” scripts when needing to investigate day-to-day Production DBA challenges. One of these scripts goes back to my Junior-DBA days when the Senior-DBA challenged us to write a script to help manage transaction logs. One of the cool things about working with SQL Server is the community and how that community is helpful and accommodating to its members. Posting questions in StackOverFlow, Twitter or the #SQLHelp Slack Channel, there is always someone willing help, to chime in with their experience and offer viable solutions. Many of the scripts in my ToolKit have been shaped by folks in the community and what they have posted. If you have ever worked with a different RDBMS, it makes you very appreciative of the SQL Server community.
I have put this code in a SQL Notebook. If you have never used SQL Notebooks, I hope you will give them a chance. I have created SQL Notebooks to share amongst the team and making the sharing of diagnostic data easy. Here is a great video on SQL Notebooks from Rob Sewell.
SQL Notebooks in Azure Data Studio for the DBA -Rob Sewell
Download link to this notebook is at the bottom of this post.
I want to provide a quick break down of the different code snippets included in this notebook.
Transaction Log Management
This script was written as a challenge from my Senior DBA (Sean McCown) at the time, to all the junior and mid-level DBAs to help us understand T-Log Management
Backup and Restore Estimated Completion Time
Sometimes you want to know how much longer a backup or restore statement will take. This will give you a rough estimate.
Backup History
Query to find your backup history. I based my initial script from this Microsoft query. Last Backup
Find the last backup of all DBs on an instance
Finding the Size of your Databases
I have seen a lot of folks do this in the GUI. Why do that when it is so easy via code.
There are three result sets.
SQL Server has three states that can be assigned to a query..
If the percentage is much higher for the Signal waits, then we need to determine if the bottleneck is the CPU processors themselves.
Fixing Orphaned Users
When using SQL Auth, orphaned users can be an issue when restoring database from one server to another.
Statistics
Find the current state of your statistics for a specific database.
Cursor Syntax Example
I never can remember cursor syntax and I always have to look it up. Cursors are not bad, if used correctly!!
Kill All Sessions for a User DB
Here is a way, using a cursor to kill sessions for a User DB. I have commented out the execute statement. If you are brand new to SQL Server might want to hold off on executing this script until you are sure you know what you are doing.
Disk Space Query
Not sure where I got this query. Pretty standard stuff.
Finding the Median Value for Query Cost from the Plan Cache
When trying to set a value for Cost Threshold for Parallelism, most folks these days will set it at 50 or 75. If you want to have more of a scientific approach, check out this query.
This will return the Median value of statement subtree cost from the plan cache for queries that went parallel. Median being the middle value when a data set is ordered from least to greatest.
I cannot take credit for this code, not sure where I got it.
Last Startup Time for all SQL Server Services
This will save you a lot of time trying to answer this question.
SQL Server Memory Consumption and Memory State
I have found diving into memory consumption in SQL Server to be a bit tricky. Here is a query that I have tweaked over the years to give me better insights into what is going on from a memory perspective.
Percentage of RollBack Complete
How long is that rollback going to take. This will give you an estimation.
AG Status
I got this query from a friend, Adrian Mioduszewski, that gives you some insight into the health of your AGs. It is most of the data that you will find in the AG dashboard.
Table Size
Fastest, most efficient way to return table size metadata.
Find TempDB Contention
How to see if anything is waiting on TempDB data pages. Queries waiting on TempDB can also be seen in the Wait_Info column of SP_WhoIsActive e.g... PFS, GAM, SGAM waits.
CPU Utilization
Straightforward way to find out how much CPU, SQL Server is consuming for the last 256 minutes.
Using SP_BlitzCache to investigate the plan cache
Using the Blitz Family of scripts is a must for any DBA.
Find the Top 10 Consuming queries in your plan cache. If you want to narrow the result set down to a specific database, then populate the @databasename variable.
Using SP_PressureDetector
Erik Darling has put together a really great SP to help you identify issues around Memory and CPU pressure. If you are having ThreadPool or Resource_Semaphore waits you are going to want to check this out.
Using SP_WhoIsActive
Working with different DBAs over the years, everyone uses SP_WhoIsActive differently. Here are some examples of how I use it.
First example is the wide-open version of the SP.
Other three examples are to find.
What are my top waits?
Glen Berry has giving us a treasure trove of diagnostic scripts and his query for looking into Wait Stats is a gem. If you are not familiar with a specific wait type, SQLSkills.com has it for you.
Happy Troubleshooting my fellow DBAs!!!
A little about me:
I do not have a computer science degree and I am not fresh out of college. I am not a “SQL MVP” or “MCM.” Just a guy in the trenches working in data. Being a DBA is a second career for me. I was a musician and music teacher for 12 years. In 2010, I was telling a friend of mine, who is a MicroStrategy developer, how I was burned-out and wanted to change careers. He told me, “Learn SQL and you will always have a job." I had no idea what SQL was. I bought a SQL Server training series and studied for about 9 months and took the SQL Server 2008 Development test. Getting that certification helped propel me into a job as a SQL Server DBA in Dallas, TX. The senior DBA that hired me was a SQL MVP and MCM. I tried to soak up as much knowledge as I could from him. I never in a million years thought I would be doing this. It is quite rewarding. I love the process of trying to solve a problem.
When evaluating whether a DBA job req is something I would be interested in pursuing, one of the questions I ask is what monitoring solution the prospective employer has in place. Being able to evaluate the performance of your SQL Servers from a historical perspective is invaluable. Monitoring tools are only the start and cannot be solely relied upon to solve your technical issues. Having a set of “go-to” scripts is necessary for any DBA regardless of how veteran you are in the field.
Over the years, I have collected my set of “go-to” scripts when needing to investigate day-to-day Production DBA challenges. One of these scripts goes back to my Junior-DBA days when the Senior-DBA challenged us to write a script to help manage transaction logs. One of the cool things about working with SQL Server is the community and how that community is helpful and accommodating to its members. Posting questions in StackOverFlow, Twitter or the #SQLHelp Slack Channel, there is always someone willing help, to chime in with their experience and offer viable solutions. Many of the scripts in my ToolKit have been shaped by folks in the community and what they have posted. If you have ever worked with a different RDBMS, it makes you very appreciative of the SQL Server community.
I have put this code in a SQL Notebook. If you have never used SQL Notebooks, I hope you will give them a chance. I have created SQL Notebooks to share amongst the team and making the sharing of diagnostic data easy. Here is a great video on SQL Notebooks from Rob Sewell.
SQL Notebooks in Azure Data Studio for the DBA -Rob Sewell
Download link to this notebook is at the bottom of this post.
I want to provide a quick break down of the different code snippets included in this notebook.
Transaction Log Management
This script was written as a challenge from my Senior DBA (Sean McCown) at the time, to all the junior and mid-level DBAs to help us understand T-Log Management
Backup and Restore Estimated Completion Time
Sometimes you want to know how much longer a backup or restore statement will take. This will give you a rough estimate.
Backup History
Query to find your backup history. I based my initial script from this Microsoft query. Last Backup
Find the last backup of all DBs on an instance
Finding the Size of your Databases
I have seen a lot of folks do this in the GUI. Why do that when it is so easy via code.
There are three result sets.
- Total Database Size for the instance
- Database Size for each Database
- Size of each Data File with a Running total per Database
SQL Server has three states that can be assigned to a query..
- Running
- query that is currently running on a CPU
- Runnable
- query that is ready to execute but waiting for an available CPU
- Suspended
- query is waiting for a third-party resource to become available
- Resources waits refers to query waiting on some resource to go to the runnable queue
- Signal waits refers to a query waiting to move onto the CPU with a state of runnable
If the percentage is much higher for the Signal waits, then we need to determine if the bottleneck is the CPU processors themselves.
Fixing Orphaned Users
When using SQL Auth, orphaned users can be an issue when restoring database from one server to another.
Statistics
Find the current state of your statistics for a specific database.
Cursor Syntax Example
I never can remember cursor syntax and I always have to look it up. Cursors are not bad, if used correctly!!
Kill All Sessions for a User DB
Here is a way, using a cursor to kill sessions for a User DB. I have commented out the execute statement. If you are brand new to SQL Server might want to hold off on executing this script until you are sure you know what you are doing.
Disk Space Query
Not sure where I got this query. Pretty standard stuff.
Finding the Median Value for Query Cost from the Plan Cache
When trying to set a value for Cost Threshold for Parallelism, most folks these days will set it at 50 or 75. If you want to have more of a scientific approach, check out this query.
This will return the Median value of statement subtree cost from the plan cache for queries that went parallel. Median being the middle value when a data set is ordered from least to greatest.
I cannot take credit for this code, not sure where I got it.
Last Startup Time for all SQL Server Services
This will save you a lot of time trying to answer this question.
SQL Server Memory Consumption and Memory State
I have found diving into memory consumption in SQL Server to be a bit tricky. Here is a query that I have tweaked over the years to give me better insights into what is going on from a memory perspective.
Percentage of RollBack Complete
How long is that rollback going to take. This will give you an estimation.
AG Status
I got this query from a friend, Adrian Mioduszewski, that gives you some insight into the health of your AGs. It is most of the data that you will find in the AG dashboard.
Table Size
Fastest, most efficient way to return table size metadata.
Find TempDB Contention
How to see if anything is waiting on TempDB data pages. Queries waiting on TempDB can also be seen in the Wait_Info column of SP_WhoIsActive e.g... PFS, GAM, SGAM waits.
CPU Utilization
Straightforward way to find out how much CPU, SQL Server is consuming for the last 256 minutes.
Using SP_BlitzCache to investigate the plan cache
Using the Blitz Family of scripts is a must for any DBA.
Find the Top 10 Consuming queries in your plan cache. If you want to narrow the result set down to a specific database, then populate the @databasename variable.
- CPU
- Reads
- Memory Grant
- Executions Per Minute
Using SP_PressureDetector
Erik Darling has put together a really great SP to help you identify issues around Memory and CPU pressure. If you are having ThreadPool or Resource_Semaphore waits you are going to want to check this out.
Using SP_WhoIsActive
Working with different DBAs over the years, everyone uses SP_WhoIsActive differently. Here are some examples of how I use it.
First example is the wide-open version of the SP.
Other three examples are to find.
- Lead Blockers
- Help find most read intensive query
- Help find most CPU intensive query
What are my top waits?
Glen Berry has giving us a treasure trove of diagnostic scripts and his query for looking into Wait Stats is a gem. If you are not familiar with a specific wait type, SQLSkills.com has it for you.
Happy Troubleshooting my fellow DBAs!!!
dbatoolkit.ipynb | |
File Size: | 63 kb |
File Type: | ipynb |