SQL PerformanceHi again,
I saw an article a while back that showed how to calculate uptime on your SQL Server by utilizing a system SPID that is always on, since the beginning of a boot and is never released. That would be spid 1. So, knowing that, the author of that article decided he would get the uptime. Here’s the code, check it out!
I’ll be providing a follow up to this article in the near future that shows how to catalog the record count over time and provide a trending analysis, but for now, here is some simple code to get the number of rows, data size, index size and more for all the tables in your database. Enjoy!
I’ll say loud and proud, the software I work on uses a lot of dynamic SQL, not by choice, but it just does. Over the years, we’ve become more adept at learning more about how SQL behaves, and it is true, cache hit ratio is an imporant topic. However, for various reasons, way to lengthy for the purpose of this topic, dynamic SQL can cause your query plan to be recompiled (along with a host of other reasons). So, for some of our customers who have seen high CPU usuage and low cache hit ratio we decided to turn on Forced Paramterization, and we saw an immediate drop in CPU utilization, by as much as 40% for a single customer’s system.
I’d like to talk briefly about the STATISTICS feature in SQL 2005. I recently worked on an issue for a colleague who presented a SQL statement to me that ran poorly. He had indicated to me that he ran this statement through the Database Tuning Advisor and it recommended an index, however, when he added the index he received a negligible gain, if nothing at all.
Hi all,
Just a few minutes to write this, but I thought I’d take a moment to update my blog with an interesting set of procedures. SQL 2005 gives you sys.dm_exec_sql_text, which can be combined with sys.sysprocesses to get some interesting results. When you run them in combination you can get the actual sql command that is being run at the moment. It would seem, even DBCC INPUTBUFFER only gives you the procedure level, not the statement level. So here is the code:
SQL SERVER 2005 Profiler Trace Template (for RML)
This is a trace template that captures all events necessary for replaying the trace on a backup database.
Choosing the right raid level for SQL Server takes careful planning. For example, do you require high performance reading data, but not writing?
