SQL Server Tutorials and Tips

Monitor Running Processes in SQL Server 2005/2008

You often need to monitor the processes running on the server in order to improve the performance, by tuning them. A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

select
source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time
from sys.dm_exec_query_stats as stats
cross apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text
order by total_elapsed_time desc

The above code will list out the queries based on the descending order of the time they take

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical
information's about cached data. The sys.dm_exec_sql_text is the another view that
gives actual text of the sql_handle which is in binary format. It converts the binary
to actual characters

You can also make use of an article from the Microsoft available at
http://msdn.microsoft.com/en-us/library/ms191511.aspx

Further Reading:

Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server

SQL Server: Increment an AlphaNumeric Number‏

Your columns may have a categorization scheme in the form of an alphanumeric number. and it may be needed to have a customized incremental value for this scheme. Consider that you want to have numbers in the series like ABC1, ABC2, ABC3 etc. In SQL Server, there can be many methods to do this.

In this post, I will show two methods to increment an Alphanumeric Number in SQL Server

Method 1 : Derive it in a SELECT statement

declare @t table(id int identity(1,1), names varchar(100))
insert into @t(names)
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5'
select 'ABC'+CAST(id as varchar(10)) as id,names from @t

The above method concatenates the string with the identity column. This produces the following results:

Method 2 : Use Derived column in the table

declare @t table(id int identity(1,1),
alpha_id as 'ABC'+CAST(id as varchar(10)), names varchar(100))
insert into @t(names)
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5'
select * from @t

Using this method, the T-SQL code concatenates the string ABC with identity column as soon as data is added to the table.

OUTPUT

SQL Server: Monitor Long SQL Agent Jobs

In SQL Server, there are various jobs running on the server, each solving a different purpose. Sometimes these jobs run for a long time. The best way to identify and monitor these long running jobs is to make use of a profiler.

Let us assume, you have the following code:

While 1=1
print 1

When you schedule this as a job, the code never finishes it's execution, as the code does not have any condition to break the loop. So it would be a never ending job that may consume full memory to execute the endless loop.

Note: This is just an example to emulate a long running job.

When you run a profiler, you can identify jobs which have ‘Batch Starting’ class with no corresponding ‘Batch Ending’ Class. Also the columns CPU, Reads, Writes etc. will be empty. As you can see in the profiler report below, the row with textdata while 1=1 print 1 is a long running job. See the highlighted row below:

Also if the values of columns CPU, Reads, Writes, etc. are huge in numbers, there is a chance that a job is running for long time. The following is an example of a long running, but which finishes its execution after some time.

As you see in the profiler result, the values for the columns CPU, Reads, Writes etc. are high which means these are also long running jobs

Important Notes:

1. Do not run the above examples in a production server, as they will consume a lot of memory
2. After trying the above examples as a job, make sure to delete those jobs.

Transfer Logins from SQL Server 2005 to SQL Server 2008

When you want to move a database from one server to another, you can take a backup of the database and restore into the other server. However this will not transfers the logins from one SQL server to another, say SQL Server 2005 to SQL Server 2008. Instead users may receive the following message on SQL Server 2008, when they try to log in:

To transfer the logins, the most reliable method in my opinion is to make use of the sp_help_revlogin stored procedure shown in this article http://support.microsoft.com/kb/918992

Assuming you have copied the script from the above link, just run the following code in SQL Server 2005

EXEC sp_help_revlogin

This command will generate the create logon script based on the logins available in the SQL Server 2005. Here’s a sample:

All you need to do now is go to the SQL Server 2008 instance, start SQL Server Management Studio, connect to the instance where you moved the database and execute the login script you generated in the previous step.

Note: If the logins should be transferred to a server with a different domain, you may need to specify the domain name in the script generated.

SQL Server: First and Last Day of Year, Quarter, Month and Week

With so many questions floating around calculating the first and last day of a given duration in SQL Server, here’s a post that explains the technique of calculating the first and last day of a given duration. We will cover the following:

- Calculate First and Last Day of the Year
- Calculate First and Last Day of the Quarter
- Calculate First and Last Day of a Month

First and Last Day of Year

To get the first day of the year, we are using the DATEDIFF function to determine the number of years from ‘1/1/1900’ to the current date (GETDATE). We then add this calculated number result (in this case 111) to ‘1/1/1900’, to obtain the first day of the year.

Similarly, a simple trick is adopted to get the last day of the year. We get the first date of the ‘next year’ and simply subtract one day from it. So the expression SELECT DATEADD(yy, DATEDIFF(yy,'',GETDATE()) + 1, 0) gets you the first day of the ‘next’ year. Subtracting 1 from it gives you the last day of the current year.

The same approach is adopted to find the last day of the quarter and the month.

First and Last Day of the Quarter

First and Last Day of a Month

Here are all the three queries for you to try out:

-- First and Last Day of Year
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
as 'First Day of Year'
SELECT DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, 0)) as 'Last Day of the Year'

-- First and Last Day of Quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
as 'First Day of Quarter'
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0))
as 'Last Day of the Quarter'

-- First and Last Day on Month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
as 'First Day of Month'
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
as 'Last Day of Month'

OUTPUT

SQL Server: Disable Table Constraints (all or some)

Constraints let you define a way to automatically enforce the integrity of a database. A table constraint is declared independently from a column and can be applied to more than one column in a table. Sometimes you may need to disable one or all table constraints, in order to import data, truncate tables etc.

You can use the following methods:

Consider these tables:

Suppose you want to import employee details to the table emp_details without having any corresponding data in emp_master:

INSERT INTO emp_details(emp_id) SELECT 34

This statement will throw an error as the value 34 in not available in the table emp_master

Msg 547, Level 16, State 0, Line 10
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__emp_detai__emp_i__1ED998B2". The conflict occurred in database "master", table "dbo.emp_master", column 'emp_id'. The statement has been terminated.

A Table constraint is in effect! Here to know the constraint name, run this code:

EXEC sp_help emp_details

and see the sixth resultset which shows the constraint name.

Using this name, you can now disable the constraint

ALTER TABLE emp_details
NOCHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Now if you re-run the code,

INSERT INTO emp_details(emp_id) SELECT 34

you will see that it gets executed successfully

To enable the constraint again, use the following code:

ALTER TABLE emp_details
CHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Disable All Constraints in a Table

To disable all constraints at a time, use the following code:

EXEC sp_msforeachtable 'alter table ? nocheck constraint all'

To enable all constraints at a time, use the following code

EXEC sp_msforeachtable 'alter table ? check constraint all'

Note that sp_msforeachtable is undocumented and alternatively you can use a cursor or a while loop to get the constraints and disable or enable them.

Some Points to be considered:

If you disable a constraint for some reasons, make sure to enable them later
Primary key constraint can not be disabled
Foreign key and Check constraint will be disabled using the nocheck command

SQL Server Denali Resources

Microsoft SQL Server Code-Named “Denali” is the next release of Microsoft SQL Server. With the CTP1 release, there has been a lot of buzz in the communities about it. If you are new to SQL Server Denali, here are some resources to get you started:

Download SQL Server Denali CTP1 Evaluation (iso)

Download SQL Server Denali (x86)

Download SQL Server Denali (x64)

What’s New in SQL Server Denali

SQL Server Denali Books Online/Documentation

Sample Database AdventureWorks for SQL Server Denali CTP1

SQL Server Denali System Requirements

I will update this post as and when I find more resources.

T-SQL, SQL Administration and SSRS Articles Link List – March 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of March 2011

SQL Server Administration Articles

Repair SQL Server Database marked as Suspect or Corrupted - There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. This post shows how to resolve this issue.

SQL Server: SSMS 2008 Intellisense Stops Working after Installing VS 2010 SP1 - After installing VS 2010 SP1, Intellisense stopped working in SQL Server Management Studio SSMS 2008 and R2 databases. This post has some solutions.

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them - Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the methods shown in this post.

SQL Server: Move Table to a new File Group - This article shows why and how to move a SQL Server Table from one File group to another.

SQL Server: Delete Backup History to reduce MSDB database size – This article demonstrates the use of the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete backup history that is older than the specified date.

T-SQL Articles

SQL Server: Search Non Round Numbers - Here’s a simple query that searches all non round numbers from a table. The following are non-round numbers 20.00, 24.0 and the following are round numbers 20.20, 24.42 and so on.

SQL Server: Combine Multiple Rows Into One Column with CSV output - In response to one of my posts on Combining Multiple Rows Into One Row, SQLServerCurry.com reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output. This post explains how to do so.

SQL Server: Insert Date and Time in Separate Columns - If there is a need to store date and times values in separate columns, you can store Date values in the Datetime column and Time values in either the char datatype or the time datatype (Sql Server 2008), as shown in this post

SQL Server: Group By Days and Create Categories - In this post, we will see how to Group records by Days in SQL Server and then create categories.

SQL Server: Return Boolean from Stored Procedure - Sometimes you may need to return a Boolean value from a SQL Server stored procedure. This article explains how to do so.

SQL Server: Handle Delimited String passed as a Parameter to Stored Procedure - In your application, you may need to pass multiple values as a parameter to a stored procedure. This article shows how to pass delimited values to the procedure and get data

SQL Server: Count based on Condition - Many a times, you may want to do a count in SQL Server, based on a condition. The easiest way is to use a combination of SUM and CASE as shown in this article

SQL Server Reporting Services (SSRS) Articles

SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio - In this article, we will see how to create a basic SSRS developer report using Visual Studio 2008 BI Studio. We will create a simple drill down report which will contain a total and subtotal. We will also analyze the options for reports in Visual Studio 2008.

Design Parameterized Report using SSRS 2008 - In this article, we will see how to create a SSRS report with parameters. We will create the parameters and then add these parameters to our reports, in different ways. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

SQL Server: Delete Backup History to reduce MSDB database size

SQL Server keeps a track of the backup history of your server, in the msdb database. Every time a backup or restore operation occurs on your database, additional rows are added to the backup and restore history tables. If you do not keep a check on this, you will find your msdb database growing over time.

SQL Server provides the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete history that is older than the specified date. Here’s how to use this stored procedure to delete backup history that is older than Jan 31, 2011, 12:00 A.M. in the backup and restore history tables.

Similarly if you want to automatically delete records that is say 2 months old, create a stored procedure that calculates the date and executes the sp_delete_backuphistory procedure

Here's the same query for you to try out:

CREATE PROCEDURE [dbo].[DeleteBackupHistory]
AS

DECLARE @BckDate DATETIME
SET @BckDate = CONVERT(varchar(10), DATEADD(dd, -60, GETDATE()), 101)

EXEC sp_delete_backuphistory @BckDate

Now whenever you want to delete records 2 months prior to the current date, just call the DeleteBackupHistory stored procedure.

Similarly, you may also want to look at the sp_purge_jobhistory and sp_maintplan_delete_log to remove other history information and keep your msdb database from growing over time.

SQL Server: Count based on Condition

Many a times, you may want to do a count in SQL Server, based on a condition. The easiest way is to use a combination of SUM and CASE as shown in this example

Here’s the same query to try out

DECLARE @TT Table (
CourseID int, StudentID int, EnrolledBy varchar(25)
)

INSERT INTO @TT
SELECT 1, 1, 'Hrishi' UNION ALL
SELECT 1, 2, 'Sagar' UNION ALL
SELECT 1, 3, 'Tony' UNION ALL
SELECT 1, 2, 'James' UNION ALL
SELECT 1, 4, 'Krish' UNION ALL
SELECT 2, 5, 'Tony' UNION ALL
SELECT 2, 5, 'Molly' UNION ALL
SELECT 2, 6, 'Tony'

SELECT
COUNT(*) as 'Total Enrolled',
SUM(CASE
WHEN EnrolledBy = 'Tony'
THEN 1 ELSE 0 END) as 'EnrolledbyTony'
FROM
@TT

In the query shown above, we are using SUM and CASE to count data only for those records, which are Enrolled by Tony. Similarly instead of SUM, you can also use COUNT which only counts the non-null values.

OUTPUT