The news is out. The consensus on using hints is “Do not use them”. They will ruin you. But much like the 1950’s people keep their dirty laundry to themselves and outwardly show their best “non hint” selves, this applies as well. With that said, I will go ahead and use the disclaimer that you SHOULD NOT use hints. But, if you ARE like me, and touch a lot of SQL code where there is a huge difference in site performance between a 1 second execution and 20ms execution, then sometimes you don’t have much of an option. With that said, I will talk about the optimizer. It’s getting smarter and smarter at dealing with the majority of queries and keeping them within an even keel baseline. But that comes at a cost. It can’t take as many chances so it plays it safe. And this is where you come in. But before you go around adding hints everywhere in your system (which is a horrible idea). You need to know how to write code so that it will not NEED hints. To do this is very simple. The answer is KEEP IT SIMPLE. In other words, keep the queries simple. Do not create huge SQL Statements. It’s much better to break the queries up so that you do not give the optimizer many different routes to take. Breaking them up typically entails materializing the data into temp tables (write as little data as possible) so you can break up […]
Continue reading ...
-
Posted on May 20, 2015 by Sripal
-
1) FIND THE CULPRITS Like other software’s, we need to understand that MS SQL server is also a computer program but a complex program. Here Microsoft written this complex program. So if there is any problem with the SQL server, we need to understand why this program is not running as we expected. From SQL Server we need to pull and push data as fast & accurate as possible. If we face any issues, reasons may be SQL Server (complex program) needs certain hardware and installation settings which we are not providing properly. The way SQL Server implemented and the way it understands T-SQL code, we are not providing proper T-SQL code to it Even though MS SQL Server is a proprietary software, they provided us a lot of ways to understand the Server and what’s going on inside so that we can use it efficiently. If the SQL server is running without errors, first we need to calculate wait statistics of different threads. SQL server uses threads for every user request. Again a thread is nothing but another program inside complex program which is called SQL server (This thread is not Operating system thread on which SQL server installed. This is related to SQLOS thread which is a pseudo operating system for the SQL Server). We can find wait statistics using “sys.dm_os_wait_stats” DMV. There are many scripts online to query this view as per your needs. I like Paul Randal script(http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts) [cc lang=”sql] WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] […]
Continue reading ...
There are a lot of different design patterns that lend themselves to creating the shortest path to the data. One of the most efficient is the caching of stored procedure result sets. In order to do this, we need to read the incoming parameters and create a cache key. This cache key is then stored along with the stored procedures result set as a unique identifier representing that combination of the stored procedures parameters. The caveat with this method is that the business requirement needs to allow stale data. There are times where you will need to use values other than the passed in parameters in order to create the cache key. Some examples include datetime data types or keys that are unique (like a customerkey). If the hash that gets created from the parameters is unique, then you will never reuse that dataset again. With this in mind you would even have determine whether the procedure is even cacheable. Another concern to keep in mind is the duration of time you can serve stale data. Maybe 30 seconds, 1 minute, or 1 hour? Any time increment is able to be worked with by clearning the cache tables at the desired interval. Design Let’s look at the basic workflow for how this procedure will work. First of all, we will need to hash all the parameters that are coming into the procedure (unless they are unique in which case we may not be able to cache, or we can possibly […]
Continue reading ...
One of the possible bottlenecks for a busy SQL Server is contention in TempDB. This symptoms of this bottleneck are mostly excess wait_types of PAGELATCHEX that are blocking one another. (you can use sp_who3 in order to see whether those wait types are popping up during your busy times). The cause of this is either implicit or explicit use of TempDB. Explicit being creation of table variables and temp tables, or implicit being worktables being generated by complex execution plans. Luckily there are a number of steps you can take in order to overcome these issues. 1) Enable traceflag 1118 There is some argument as to whether this trace flag actually makes a difference. first of all, MS SQL CSS engineers state it’s already the default for 2008. What the flag does is immediately allocate space for tempdb tables upon creation rather than on insert (if I understand it correctly). It did seem to help our database during one point of contention, but I’m not willing to test it again by turning it off. So with that said, “it can’t hurt to turn it on” at least that’s that the consensus of what I’ve read. 2) Put TempDB on a PCIE SSD The PCIE cards are getting cheaper every month. Just putting this in, reduced our write speed from 2.3ms to .3ms. What that translates into is, it’s quicker to get “in and out” of tempdb allowing less contention and higher throughput. Also while I’m too lazy to prove it, […]
Continue reading ...
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 ...
