Since I started in this trade one thing has always stood firm. Applications are slow, and will always be slow because we as a human society want results faster than they occurred yesterday. Which is funny because back in the day things happened faster for the most part. Now we have technology making everything more “seamless”, but can take longer. I love talking to our older generation. You can tell them how long something will take and it’s just an opening for them to say how the good old days were. You have to love those story’s. Anyway, I digress.. I titled this blog entry Application Overkill because I wanted to discuss how statement complications can be a bit overkill and adversily effect your application because of it.
My first IT position was supporting AOL. It was one call after another. I never even had to press a button, the call just came though. It was a bit boring because 80% of all my calls were instructing people over the phone on how to “clear the cache” in I.E. Therefore, now whenever I read or hear about cache in any aspect my AOL days resurface. I do actually laugh because at that time I thought I knew so much about computers. I was gods gift to the PC industry. When I read about Plan Cache in SQL Server, I laugh. Just like in any browser when you access a site it’s cached into the PC so the next time you want to access the site it will load faster. SQL Server has the same way of doing things. When you run a query, batch file, stored procedure, or any type of statement those are stored into the ‘plan cache’. Now, just like in IE this is normal but it can begin to cause problems. In some cases these statements need to be revamped which is normal but can become excessive, rendering SQL performance. Here is how the the SQL Lumberjacks can identify those cases and “clear the cache”.
SQL:StmtRecompile is our trusted low impact friend today. This event will capture statement level recompiles. So it’s not alone at the dance also include StmtStarting and StmtCompleted. Lets really break these down.
I’ll always highly recommend reading about these events before you execute your trace. They can speak for themselves but we all know what happens when you assume. Also, if your dealing with vendor supported databases it’s best to error on the side of more events than less. Yes, you may start rendering SQL performance a little but if you don’t include these now the vendor will ultimately ask questions, where these event captures could provided answers to. I would also include these:
- Auto Stats Event Class
- SP:Starting Event Class
- SP:Completed Event Class
- SQL:BatchStarting Event Class
To select these events in SQL Profiler you first want to select a blank template. In the Events Selection tab you need to expand the following sections to find the above events:
- Stored Procedures
As far as which data columns to select, if you place the results into a table it really does not matter. You can query to table as you please. Otherwise in the Trace Properties window click on Organize Columns. At the top you’ll see groups, move EventClass up into the Groups section. This will the collapse everything in the window so you can easily choose what you want to view.
Run the trace and let’s break this down.
I would recommend running this query during high usage hours to capture the highest level of frequent statement complications. Keep in mind this does put some load on the server. Not as much as others, but it can slow the server down depending on the build.
You can see below how Grouping the EventClass makes it easier to view.
Wow! 1382 StmtRecompile is that a lot? It’s all relative based on your baseline trace. It’s always a good idea to have a baseline trace on multiple servers and multiple applications so you have some depth to compare it to. We should be looking at the number of starting compared to the number of completed. In this case the of the 34049 that were started 1382 were recompiled. This is a pretty good average. We would like to see a low number of recomplies compared to the number started. If the number of recompiles is over 75% of the number started that should cause some concern.
You can open each of the sections and review all the different columns. What you need to pay close attention to is the EventSubClass section. You will come across ‘Statistics Changed’ and ‘Deferred compiles’. These are your heavy hitters that cause a recompile. However, several other parameters can cause recompiles. If you notice a large number of one parameter then you need to look closely at that specific one. It’s also a good practice to look at the different statements and objects. You may find there is a relationship between the two that is causing problems.
The next time you find your databases taking a long time or the next time you find an application taking forever just remember, you can find the cause. The cause could be easily fixed, it could be the server is old and slow, could be the application having a hard time recompiling events. As SQL Lumberjacks we need to remember, especially dealing with vendor supported applications we can’t always fix it. We can always, without a shadow of a doubt find the problem. We as SQL Lumberjacks have to be in sales sometimes and convince the vendor to buy into your findings and agree to fix the problem, or in this case ‘Clear the Cache’.