It is common to see high worker count issue on the production server and it can have a range of negative impacts on SQL Server performance and stability, potentially leading to degraded user experience and even downtime. Some of the common reasons for high worker thread issues in SQL Server. Blocking Sudden workload spike Resource […]
Fastest Way to Find Row Count of All Tables From All Databases in SQL Server
Read more
There are many times you as DBA or Developer needs to get all row count from all tables or row count of all tables from all Databases in a SQL Server. You may need a quick way to count the number of rows contained within the tables. The following SQL code will get you the row […]
Which are the queries using a particular index or table?
Read more
The DMV sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats give you excellent detailed information about how the indexes are being used and what operations are occurring (select, insert, update, delete) in the database. But they don’t tell you a particular index or table is being used by which all the queries. So, when you want to tune the existing […]
Find Missing Indexes For All SQL Server Databases
Read more
DMV (Database Management View) and DMO (Database Management Objects) were added in 2005. There have been huge improvements in each new edition of SQL Server. It provides us a lot of useful information about SQL Server like – expensive queries, wait type, missing index…., and the list goes on and on… In this blog, I […]
Finding Input Parameters in an Execution Plan
Read more
The query execution plan is a map of work which SQL Server is going to perform to execute a query. It provides a lot of useful information like operators, cost, estimated rows, actual rows, predicate details, parallelism, and much more information to troubleshoot the performance issue. During the troubleshooting of the performance issues, I would […]
Figuring out Time-Out Prone queries
Read more
Recently, I was helping one of the community attendees on the SQL Server Query Time-Out issue. Once we are done with troubleshooting, he asked a very excellent question; “I am dealing with very critical OLTP systems. As my company has been expanding the business, the data is rapidly growing. It is causing a lot of […]
SQL Server 2017 – DMV – sys.dm_os_host_info
Read more
I always love to get my hands dirty with new features of SQL Server. I was exploring SQL Server on Linux, and at a point, I had to check the operating system details of the Linux system. Being lazy to figure out Linux OS command to check the operating system details, I started figuring out […]
SQL SERVER DATABASE FILES WISE I/O LATENCY PART#2
Read more
Since the DMF sys.dm_io_virtual_file_stats shows cumulative I/O statistics value for the database files, you can’t just use the DMF to shoot a conclusion that the database files are experiencing high latency issue. The reason is the overall aggregation will mask spikes of poor performance. It is recommended to capture the latency data over a period […]
SQL Server Database Files Wise I/O Latency Details PART#1
Read more
One of the real-world I/O troubleshooting problems is – If you have your databases spread across multiple LUNs and you want to find out which database files are experiencing high latency issues and driving the most I/Os to storage subsystem using performance counter (Avg. Disk sec/Read & Avg. Disk sec/Write). The counter doesn’t help you to identify […]
SQL Server 2017 – DMF – sys.dm_db_log_info
Read more
The release of Microsoft SQL Server 2017 brought a lot of very interesting new features. One of them is the addition of DMF sys.dm_db_log_info. It allows having a look at the structure of the transaction log without using the undocumented DBCC LOGINFO command. DMF – sys.dm_db_log_info It returns VLF information of the transaction log file […]