SQL Azure: Troubleshoot and Optimize Queries using DMV’s – Free Whitepaper

Microsoft recently published a whitepaper that provides guidelines on the Dynamic Management Views that are available in SQL Azure, and how they can be used for troubleshooting purposes.

Quoted from the document:

SQL Server generates an optimized query plan for all the queries that are executed. This allows the SQL Server optimizer to reuse the query plan when the same or similar query is executed to retrieve the data in the fastest time possible. As the data and the statistics on that data change, the query plans become out of date and can become inefficient. It is important to identify these queries and tune them for optimal performance of the application and consistent user experience. The DMVs just discussed directly help in identifying the problematic queries

Download the whitepaper: Troubleshooting and Optimizing Queries with SQL Azure

Remove and Replace Special Characters in a SQL Server Column

Here’s a sample output of a SQL Server Column containing special characters, obtained from different sources.

As you can observe, the format of the numbers is not uniform. What is needed is a uniform format like 111-111-1111

Here’s how to remove and replace these special characters using REPLACE to obtain the desired output:

-- SAMPLE DATA
DECLARE @TT TABLE (Phone varchar(15))
INSERT INTO @TT VALUES
('(100)-111-2222'),
('(101)111-2222'),
('111-111-2222'),
('(110)-100-2222'),
('(111)111-2222'),
('112-111-2222'),
('(121)111-2222')

-- QUERY
SELECT
REPLACE(
REPLACE(
REPLACE(
Phone,
'(', '' ),
')-', '-' ),
')', '-' ) as Phone
FROM @TT

OUTPUT

Note: This approach works well when the number of characters to be replaced are few.

Find the Closest Number using SQL Server

In one of the queries I was writing, I had to find out the closest match to a given value. In my case, it was a Price column and given a value, I had to find the prices of items that closely match it.

Here’s how it can be done using the SQL Server ABS function

-- SAMPLE DATA
DECLARE @TT TABLE (ID int, Price float)
INSERT INTO @TT VALUES (1, 23.29)
INSERT INTO @TT VALUES (2, 91.33)
INSERT INTO @TT VALUES (3, 78.45)
INSERT INTO @TT VALUES (4, 25.26)
INSERT INTO @TT VALUES (5, 11.13)
INSERT INTO @TT VALUES (6, 3.22)
INSERT INTO @TT VALUES (7, 29.33)
INSERT INTO @TT VALUES (8, 88.34)
INSERT INTO @TT VALUES (9, 48.44)
INSERT INTO @TT VALUES (10, 38.39)

-- QUERY
DECLARE @input int
SET @input = 25
SELECT TOP 3 ID, Price from @TT
ORDER BY ABS(Price - @input)

OUTPUT

SQL Server 2008 R2 Best Practice Analyzer – Free Tool

Microsoft recently released the SQL Server 2008 R2 Best Practice Analyzer

As quoted by Microsoft “BPA is designed for administrators who want to determine the overall conformance of their SQL Server instances and topology with established best practices. BPA examines your SQL Server instance for system events, reviews login permissions, scans your SQL Metadata for advisable settings, and recommends solutions to items that do not conform with established Microsoft best practices”

The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

• Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
• Determines if the configurations are set according to the recommended best practices
• Reports on all configurations, indicating settings that differ from recommendations
• Indicates potential problems in the installed instance of SQL Server
• Recommends solutions to potential problems

Download the free SQL Server 2008 R2 Best Practice Analyzer

SQL Server 2008 R2 Update for Developers – Free Training Kit

A couple of weeks ago, SQL Server 2008 R2 RTM got Released and now you have an updated Training Kit as well to get you up and running with SQL Server 2008 R2. The Microsoft Developer and Platform Evangelism (DPE) team did an amazing job of including 32 Presentations, 39 Demos, 24 Hands-On-Labs and 55 Training Videos to help you learn SQL Server 2008 R2.

As quoted from Microsoft “The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server”

Overview and Benefits
The training kit offers the following benefits:

• Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
• Provides a comprehensive set of videos, presentations, demos and hands-on labs
• Contains new content for developers who are new to SQL Server.
• Contains new content for SQL Server 2008 R2.
• Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
• Easy to download and install.

Download the SQL Server 2008 R2 Update for Developers Training Kit

You may also want to read

Introduction to SQL Server 2008 R2 Application and Multi-Server Management

Microsoft SQL Server 2008 R2 Feature Pack

Introducing Microsoft SQL Server 2008 R2 – Free eBook

TempDB Size and Monitoring

I was working on a requirement recently to monitor the TempDB to check on the space it was occupying. The requirement was to monitor and identify the TempDB space occupied by applications.

To find out the space used by all the TempDB files, use this simple query

SELECT SUM(size)*1.0/128 AS [TempDBSizeMB]
FROM tempdb.sys.database_files

However to monitor the TempDB size, I found a nice article by Sunil Agarwal called TempDB Monitoring and Troubleshooting: Out of Space to identify TempDB space allocations. Here’s the output that shows the allocations in TempDB by the currently running apps.

You can download the query here

Replace SQL Server DateTime records of a column with UTC time

Here’s how to convert a DateTime column record with UTC time

-- SAMPLE DATA
DECLARE @TT TABLE (EID int, CheckIn DATETIME)
INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')

-- QUERY
UPDATE @TT
SET CheckIn = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CheckIn)
  
SELECT * FROM @TT

OUTPUT

Before

After

You can also check my post UTC or GMT time in SQL Server 2005/2008

Introduction to SQL Server 2008 R2 Application and Multi-Server Management

Here’s a chance for you to get access to this FREE two-hour E-Learning course that explores the capabilities of Introduction to SQL Server 2008 R2 Multi-Server Application Management.

This course covers the following topics:

• Introducing Application and Multi-server Management
• Working with data-tier applications
• Managing change in data-tier applications.
• Managing data-tier applications.

Clinic 10336: Introduction to SQL Server 2008 R2 Application and Multi-Server Management

Calculate Average Time Interval in SQL Server

I was recently working on a query where a Hotel has a bunch of Privileged Customers. To determine what discount to give to these customers, the query should be able to tell what is the average interval after which each privileged guest visits the hotel. The Guest who visits the hotel frequently, gets big discounts.

Here’s the query (I used Peso's code for this query which I found on the net):

DECLARE @TT TABLE (CID int, CheckIn DATETIME)

INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')
INSERT INTO @TT VALUES (6, 'April 16, 2010 8:21am')
INSERT INTO @TT VALUES (7, 'April 17, 2010 11:55am')
INSERT INTO @TT VALUES (3, 'April 22, 2010 10:16am')
INSERT INTO @TT VALUES (4, 'April 24, 2010 11:35am')
INSERT INTO @TT VALUES (7, 'April 30, 2010 9:49pm')
INSERT INTO @TT VALUES (2, 'May 01, 2010 9:49am')
INSERT INTO @TT VALUES (5, 'May 02, 2010 10:43am')
INSERT INTO @TT VALUES (6, 'April 21, 2010 9:23am')
INSERT INTO @TT VALUES (3, 'May 03, 2010 11:29am')

SELECT    CID,
        DATEDIFF(HOUR, MIN(CheckIn), MAX(CheckIn))
        / (COALESCE(NULLIF(COUNT(*), 1), 2) - 1) AS AvgIntervalInHour
FROM        @TT
GROUP BY    CID
ORDER BY    AvgIntervalinHour

OUTPUT

Determine Permission for all Users at Object Level

I was looking out to determine Permissions for all Users at an Object Level in a database. Here’s the query to do so:

SELECT
sysU.name, sysO.name, permission_name ,
granted_by = suser_name(grantor_principal_id)
FROM sys.database_permissions
JOIN sys.sysusers sysU on grantee_principal_id = uid
JOIN sys.sysobjects sysO on major_id = id
order by sysU.name 

OUTPUT

If anyone knows a better way, I would love to hear it!

Microsoft SQL Server 2008 R2 Feature Pack

Microsoft recently released Microsoft SQL Server 2008 R2 Feature Pack 10.50.1600.1. This pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server 2008 R2

It includes the latest versions of:

• Redistributable components for Microsoft SQL Server 2008 R2.
• Add-on providers for Microsoft SQL Server 2008 R2.

Check out the different items in this package

SQL Server Reporting Services Tutorials on Books Online

I have got a couple of mails this year with users requesting for basic tutorials on SSRS. Well here are some good SSRS Tutorials available freely on SQL Server Books Online (BOL) to help you get started with SSRS

Getting Started with Reporting Services - Use the tutorials in this section to learn basic report design skills, one step at a time.

How to Locate and Start Reporting Services Tools - Use this tutorial to learn how to use the tools to configure a report server, manage report server content and operations, and create and publish reports and report models.

Developer's Guide: Tutorials (Reporting Services) - Use the tutorials in this section to learn about the many different ways to develop Reporting Services solutions.

Designing and Implementing Reports Using Report Designer (Reporting Services) – Use the tutorials to build a report in Report Designer, create the report project, add data, and arrange the layout of the data and graphical elements. You can also add interactive features to your report and manipulate the output by using expressions. When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server

Designing Report Models: Tutorials - Use the tutorials in this section to learn necessary skills for creating models that you can use in Report Builder or Report Model Query Designer.

[Additional Information - 10 Must Read SQL Server Reporting Services articles on SQLServerCurry – You can also check out some advanced SSRS tutorials written on SqlServerCurry by Gouri Sohoni]

How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable

sp_MSforeachdb and sp_MSforeachtable are undocumented stored procedures in the SQL Server Databases and they can be quite useful when you need to quickly retrieve some information on your tables or databases. Do not use them too often as they may be

[Check these posts to see how these undocumented stored procedure can be used

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

List of Undocumented Stored Procedures in SQL Server]

Now while using the stored procedures, if you need to skip an object from the query, then here’s how to do so:

Skip a Database while using sp_MSforeachdb

Here’s how to skip a database while using sp_MSforeachdb. The following command prints the size all databases in a SQL Server instance

EXEC sp_MSforeachdb
'USE ?;
EXEC sp_spaceused'

If you want to skip a database say ‘tempdb’, then here’s how to do so

EXEC sp_MSforeachdb
'USE ?;
IF DB_NAME() != ''tempdb''
EXEC sp_spaceused'

Skip a Table while using sp_MSforeachtable

If you run this command, 13 rows are returned

USE NORTHWIND
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

To skip a table, let us say ‘Categories’, rewrite the following command as

USE NORTHWIND
EXEC sp_MSforeachtable
@command1='IF ''?'' != ''[dbo].[Categories]'' EXEC sp_spaceused ''?'''

This time only 12 rows are returned with the table Categories skipped.

To skip multiple tables, you can rewrite the same query with the NOT IN clause

USE NORTHWIND
EXEC sp_MSforeachtable
@command1='IF ''?'' NOT IN (''[dbo].[Categories]'',''[dbo].[Customers]'')
EXEC sp_spaceused ''?'''

Find the Next Identity Value of Each Table of your SQL Server Database

Here’s a quick (and dirty) way of finding out the next identity value of each table of your SQL Server Database. We will use the undocumented stored procedure sp_MSforeachtable for this purpose

USE Northwind
GO
EXEC sp_MSforeachtable
'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
BEGIN
Print ''?'' DBCC CHECKIDENT (''?'', RESEED)
END'

OUTPUT

Observe how I have used an IF condition to only check those tables which have an Identity. Usually when developers do not add this step, they get error messages for those table that do not contain an identity column as shown below

As I said, it is a quick and dirty way! Although the undocumented stored procedures are helpful, use them sparingly as they may be deprecated and removed from future SQL Server versions.

If you liked this post, you can also read my other post 8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable for similar tips

Winners of the SQL Server MVP Deep Dives EBook

Some time back I had posted that my site DotNetCurry.com is celebrating 3 years and we have kept a Mega Giveaway of Books and products. There are some EBooks from Manning to be won including 2 copies of the SQL Server MVP Deep Dives worth $34.99 each.

The two winners (twitter followers) have been declared and they are Kensimmons and SQLPrincess. Congratulations! You will be contacted via Twitter within the next 48 hours. Make sure you check your Twitter Direct Messages from @DotNetCurry