Tag Archives: SQL - Page 2

SQLPerformance.com from SQLSentry

I was pretty excited to see the launch of a new website from SQLSentrySQLPerformance 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.

Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()

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.

Read more »

XQUERY – Examples and Scripts

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!

XQuery Labs – A Collection of XQuery Sample Scripts

Jun 26 2008 3:41PM by Jacob Sebastian

Read more »

SQL Script to Review Backup and Restore Processes

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.

Read more »

Scripting User Database Rights

All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.

Some things to note:

  • Script does not remove or reduce rights if the user already exists.
  • Create user statements that test if users already exits.
  • Add users to existing database roles.
  • Scripts object level grants.

Read more »

SQL Skills – Use Outlook as Your RSS Viewer

RSS feeds in Outlook

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. 

Read more »


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.

Read more »

Query Plans and active query information

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.

Read more »

Disk Space – Get table sizes

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.

Read more »

T-SQL Using Parameter Table like a Cursor

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.

Read more »