Tag Archives: SQL Server - Page 3

Disk Space – Finding I/O Bottlenecks(SQLIO)

Posted by on January 10, 2012 Comments Off on Disk Space – Finding I/O Bottlenecks(SQLIO)

As a production DBA , I get to start diving into the internals of the SQL Server environment. Over the last year, we had an opportunity to ‘let loose’ on a new clustered environment with a dedicated SAN.  Zoom… Zoom…Zoom! It was like performing sea trials or a shakedown tour in a new ship. Much of the testing centered around the execution of existing SQL code and processes. We were able to set-up a series of tests using the  Microsoft ‘s  SQLIO Disk Subsystem Benchmark Tool to saturate the disk subsystems.

 The tool does not actually test your SQL Server configuration , but provides the means to script out and execute a controlled load against the disk subsystem. Scripts can be executed to create a variety of read and write operations. Parameters allow the load to use a variety of file sizes, number of threads, latency, outstanding requests in random or sequential reads/writes for specific durations.   I am not going to get into the specifics of the scripts we used. The links below provide a much better examples than I could tap out in this post.

Read more »

Disk Space – DBCC UPDATEUSAGE

Posted by on January 7, 2012 Comments Off on Disk Space – DBCC UPDATEUSAGE

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

Posted by on December 29, 2011 Comments Off on 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

Posted by on December 29, 2011 Comments Off on 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

Posted by on February 3, 2010 Comments Off on 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 »

Except and Intersect operators in SQL Server 2005/2008 (from decipherinfosys)

Posted by on April 30, 2009 Comments Off on Except and Intersect operators in SQL Server 2005/2008 (from decipherinfosys)

Good Explanantion and usage examples of the Except and Intersect  Operators in SQL 2005 ( 2008).

From decipherinfosys
In SQL Server 2005, MSFT introduced two new operators in their T-SQL language: Except and Intersect. We had briefly touched upon these while discussing the MERGE command in Oracle – you can read more on that post here. EXCEPT returns the distinct data value from the left query (query on left side of the operand) which does not exist in the right query (query on the right side of the operand). INTERSECT returns data value which is common in both queries. In Oracle, the equivalent of the EXCEPT operator is MINUS and INTERSECT is same as INTERSECT. In SQL 2005, using EXCEPT and INTERSECT, one can also simulate the MERGE command in Oracle (see the blog post that is mentioned above).

Read Full Post Here

SSRS – Format() and FormateDateTime()

Posted by on April 15, 2009 Comments Off on SSRS – Format() and FormateDateTime()

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.

http://thavash.spaces.live.com/blog/cns!CF6232111374DFD2!155.entry

April 10

Working with Dates in Reporting Services

As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today , “How to I format dates when using the Date Picker and sending it through to a stored procedure” ? Let’s have a look ….
1) The FormatDateTime command
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :
Command Result
FormatDateTime(Parameters!Date.Value,1) Tuesday, April 10, 2007
FormatDateTime(Parameters!Date.Value,2) 4/10/2007
FormatDateTime(Parameters!Date.Value,3) 12:00:00 AM
FormatDateTime(Parameters!Date.Value,4) 00:00

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

Command Result
Format(Parameters!Date.Value,”dd-MM-yyyy”) 10-04-2007
Format(Parameters!Date.Value,”dd/MM/yyyy”) 10/04/2007
Format(Parameters!Date.Value,”MMM-dd-yyyy”) Apr-10-2007
Format(Parameters!Date.Value,”MMM-dd-yy”) Apr-10-07

So 3 M’s give you “Apr” ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)

Jeff’s SQL Server Blog – Proper Case UDF

Posted by on February 5, 2009 Comments Off on Jeff’s SQL Server Blog – Proper Case UDF

Using My Favorite Tech Service –http://www.Google.Com.

I Found this Posting for a Proper case T-Sql User Defined Function on Jeff’s SQL Server Blog — Go to the post as it has an port to MySQL and a couple of other ‘ADVANCED’ examples in the comments.

From the original Blog :

I posted this one a long time ago and needed to use it today, so I thought I’d post it up here as well in case anyone finds it useful.  This simply attempts to capitalize the first letter of each word for the string passed in.  Use it to help clean up some pre-existing data, but don’t use it as a way of presenting your data that is stored improperly since it isn’t exact.�
Read more »

For Each … SQL System Stored Procedures

Posted by on February 25, 2008 Comments Off on For Each … SQL System Stored Procedures

Sql server has some great built-in commands procs that can help you script server or database wide commands. sp_MSForEachTable and Sp_MSForEachDb

When first looking at a database , I sometimes like to run the following:
execute sp_MSforEachTable @command1=‘Print ”?”; Select Count(*) as [? – (count)] from ?;Select Top 10 * from ? ‘

I used the sp_MSforeachDB just the other day to set all the non-system
databases on a test server to Simple Recovery mode.Just made a quick stored procedure that I passed the ? value and filter out the system databases ( Master, Model, Tempdb and MSDB).

For More Information Checkout :

November 30, 2004
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb
By Gregory A. Larsen
http://www.databasejournal.com/features/mssql/article.php/3441031