Find Disk IO Statistics DMV

This gem of a query has been indispensable in determining IO latency for our servers. The benefit of this is not having to rely on perfmon to interpret the latency (which can be a crapshoot). Here, SQL itself using SQL OS (I believe) to measure latency and usage statistics. In the past, perfmon has been sketchy in monitoring physical disk stats because it is not reliable when measuring a SAN. The old trick was to make sure perfmon’s refresh interval was below 2 second refresh in order to get good statistics. Now with this, we can see the exact latency of read / writes against the actual files. Thanks to my partner Chris for providing this. [cc lang=”sql”] SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes, CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC; [/cc] Continue reading ...

Top 3 SQL Errors That Will Leave Your Users Stranded

Over the years I’ve ran across certain situations that cause errors in SQL that error out the calling application if they are not correctly trapped. As unsuspecting as these can be, you end up learning your lesson when you get a call late at night that a web page is erroring out. Then to follow you get a deep sinking feeling in your gut while you think, “Why didn’t I think of that?”. In some of these cases I’ve often wondered why SQL doesn’t simply return a NULL or an invalid result. And often I wish there was an option that could set that because while it’s true that all errors should be handled, in truth they are not. Hopefully this list can save you a bit of heartache. divide by zero string or binary data would be truncated Invalid length parameter passed to the LEFT or SUBSTRING function These three errors are very common and all of them at one time or another have bit me in the pants – most of the time after deploying code and even worse, hours/days/months after deploying the code. Not only that, they can occur on an intermittent basis which is always fun to troubleshoot. Divide by Zero This innocent division operation turns ugly when your divisor hits the dead even zero. This is because of the rules of math. You simply cannot divide by zero. Reproducing this issue is simple enough. [cc lang=”sql”] SELECT 8 / 0 [/cc] The workaround is to […]

SQL Server Database Optimization Guide

In the troubleshooting guide we went over the different physical bottlenecks that can cause SQL Server slowness and showed how to identify them. In this guide, we’ll drill down one step deeper to help identify the actual settings or queries that are contributing to the bottleneck. By relieving these bottlenecks we will start to see our batch requests / sec increase. While it may seem you will be able to go through this article once, what is more likely is that you will need to continue to iterate through some of these principles multiple times. This is because many times when one bottleneck is relieved, another is revealed. One thing we will not cover much in this article is architecture. Unfortunately this is a much more complex subject. Suffice it to say, either you have it or you don’t. I can’t so much give architecture principles in an optimization guide, but it is probably the single most important factor in determining throughput in a system. Without a good foundation you can only build a house so large. With that said, there are still luckily plenty of optimization techniques we can employ. Missing Indexes The biggest way to be a hero in your organization is to create the missing indexes. This task is simple ever since the introduction of DMV’s. When an index is missing it contributes directly to IO usage, and the higher the IO typically means higher CPU. Each time the SQL Server service is restarted the system tables […] Continue reading ...

Comparing EXISTS vs LEFT JOIN WHERE NOT NULL

Two of the most common ways to check for the existence of records is to use a WHERE EXISTS or to perform a LEFT JOIN WHERE values are NOT NULL. My personal choice is usually to use the EXISTS method where possible. Call me a prude, but I think just by virtue of EXISTS being the keyword for this method, it’s a safer and more optimization friendly method. If someone asked me how to check for existence I would tell them to use exists. However I do think there are many more considerations that need to be outlined before making a final decision. Let’s look at examples of the two methods to illustrate: [cc lang=”sql”] — using EXISTS to check for existence SELECT P.ProductID FROM Production.Product p WHERE EXISTS ( SELECT 1 FROM Sales.SalesOrderDetail sod WHERE sod.ProductID = p.ProductID ) — Using LEFT JOIN to check for existence GO SELECT DISTINCT P.ProductID FROM Production.Product p LEFT JOIN Sales.SalesOrderDetail sod ON sod.ProductID = p.ProductID WHERE sod.SalesOrderDetailID IS NOT NULL [/cc] Since these examples are straightforward, the optimizer realizes the end result we are looking for and gives us the same execution plan for both. However we don’t always want to rely on the optimizer’s brains. This is because as the execution plan gets more complex (by adding additional tables), it’s less likely to make good suggestions. This brings up a separate point. Anatomy of LEFT JOIN WHERE NOT NULL While there is a chance both methods can take the same execution […]

Yet Another Temp Tables Vs Table Variables Article

The debate whether to use temp tables or table variables is an old debate that goes back since they were first introduced. The real answer to knowing the difference lies in what is going on under the hood and correlating those specifics to your situation. In this article we’ll touch on (hopefully all) the differences between the two. #Temp tables are just regular SQL tables that are defined and stored in TempDB. The only difference between them and a permanent table is they are not allowed to have foreign keys. You can view the temp tables currently defined via SSMS by going to TempDB and Expanding Temp Tables. Let’s look at a matrix of specific differences then we’ll touch on a few of the key differences below. Item #Temp Tables @Table Variables Can participate in a transaction Writes to Log File Writes only to memory (not disk) Can Qualify for Parallelism Allows creation of statistics Does not affect recompilation Allows nonclustered indexes Allows clustered indexes Can perform SELECT INTO Can access in nested stored procedures Can define globally Can use in user defined functions Can insert from EXEC Allows TRUNCATE Allows ALTER TABLE Scope #Temp tables can be either #locally defined (within the scope of the session) or ##globally defined (within a database). When they are locally defined, the table can be used by whatever is executed within that session (or SPID). In other words, if you define a #temp table in procedure A, then procedure A calls procedure B, […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!