SQL PerformanceHi again,
This undocumented command in TSQL reads your log file and returns results. If you’ve ever used a log reader such as the one from APEX, or any other log reader for that matter, you will see the resemblence. Here’s the syntax on how to call this particular function:
SELECT *
FROM ::fn_dblog(NULL, NULL)
Hi all, the following guide was taken from Microsoft’s best practices site. I’ve found out through the years that our product has been challenged by many of the performance problems listed in this guide, so it is definitely a good read!
You can find the Microsoft link here:
Here is the document for your reference as well: tshootperfprobs
Assuming you don’t have already have a fancy tool like Quest Software’s Performance Analyzer, or you aren’t using the Performance Dashboard offered by Microsoft, one way to quickly determine if you are CPU bound and what is actually utilizing your CPU utilization is to use a couple queries. I borrowed some information from the Microsoft document called Troubleshooting Performance Problems in SQL Server 2005 below. Read more…
Hi 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!
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:
