I was pretty excited to see the launch of a new website from SQLSentry. SQLPerformance is going to be a great resource for the SQL Server community. Congrats to SQL Server MVPs Aaron Bertrand and Kevin Kline on the launch of the site.
Read Aaron Bertrand’s announcement here.
Check out the free tool SQL Sentry Plan Explorer v1.3 . We use it a lot to review queryplans.
I needed a quick query to get some server information using sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) . It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.
The SQLOS Team has a neat article about changes in 2012 for the DMV.
There are tons of thing in this world that I really don’t understand. SQL Server XQuery is just one of those just out of reach. Poking around trying to answer a question for a developer, I found the following XML script examples. Teh writer provides excellent explanations and examples. Immediately found the right path(pun intended) to go down. I so wished my brain worked this way!
Jun 26 2008 3:41PM by Jacob Sebastian
A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.
Outlook and RSS Feed Viewer
We spend hours and hours staring at our email One of the most popular email client is MS Outlook. One thing I like to do is to add my favorite feeds to my client. It makes posts readily available and easy to share. Plus, it is a good distration to enhance your skills or just a good distraction.
One important tool in managing disk space is to make sure the system accurately reports space used by tables. DBCC UPDATEUSAGE will update the values returned by sp_spaceused.
The output of sp_spaceused “displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.” One thing to note when reviewing the output is you may see the the reserved size is disportionately larger than the data and index space used. The reserved space should always be greater, but in one case I did see > 20 GB of difference. The space reported by system can get out of whack (technical term) from repeated use of TRUNCATE statements and frequent DDL statements.
Excellent article on DBCC UPDATEUSAGE by Armando Prato.
Google search for DBCC UPDATEUSAGE.
Quick script to see active processes and to grab the query plan. It leverages some DMVs to get the query text and plan. Only can be used on SQL 2005 and above. It is a great and light wight way to see what is going on with the server and performance tuning.
Below is a script I use to determine table size. Basically, it walks through the user databases and for each table. The information is converted in GB. It’s a pretty simple use of MS sp_spaceused with sp_msforeachtable. More info on sp_spaceused.
Full script, below. Download script here.
The Google gods seem to like a very old page of mine about using Cursors in T-SQL. So, from time to time I get commnets via email. Thought I would share an alternative to using cursors.