SQL Server – Knowing the Use of Deprecated or Discontinued Features

Upgrading your SQL Server environment is inevitable and I know most many are doing this given SQL Server 2005 support will end first half next year. Whenever I get in front of customers, I have questions always coming my way around upgrades. In a recent session, I had one of the attendees send me a mail stating I have got him into trouble. I was taken aback and wrote back to know what was going wrong and how I can help him get out of trouble. This is what he wrote back:

“ Hi Pinal,

It was great to be part of your session the other day, but after the session my management had an ask which I was not sure and want your guidance.

As a DBA, I have just upgraded from a previous version of SQL Server on one of our production environment. Prior to upgrading I utilized the SQL Server 2014 Upgrade Advisor to identify deprecated and discontinued features in SQL Server 2014. Our database and development teams have regression tested the changes identified in the Upgrade Advisor for deprecated and discontinued features and remediated the items found.

As the SQL Server Administrator I need to verify all of the deprecated and discontinued features have been removed. How can I do this? I have rerun the upgrade advisor and assume that is good enough. My Management has come back strongly with an approach to make sure we are using the latest and greatest capabilities because of this upgrade and there are no legacy around.”

This was an interesting question and I wrote back to help him. This blog has been inspired by this interaction.

Thinking Simple

There are multiple options and we need a systematic way to solve this problem. We will start by doing some simple queries to DMVs to understand if we are using any deprecated features.

SELECT OBJECT_NAME,
counter_name,
instance_name AS 'Deprecated Feature',
cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%:Deprecated%'
AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC
GO

I told my DBA friend to run the above query to find out if anything is still around. I always say to have a baseline trace to rerun on an upgraded test environment to know if there are still features we need to work on. This is always not simple but this is same as what we get from Perfmon counters. These two must match. If you are not aware, here are the steps:

Open up Performance Monitor (Perfmon) and under the SQL Server counters add the Deprecated Features / Usage for all counters by selecting all and Clicking ADD.

deprecated discontinuted 01 SQL Server   Knowing the Use of Deprecated or Discontinued Features

Conclusion

As I said this was a simple solution. We also exchanged few other emails after a week of interaction. I will try to write for a different blog. Having said that, I would highly recommend using these simple techniques to know if we are using Deprecated features. I would also like to learn from my blog readers if you have ever used these features in your environments prior to upgrade and as a validation process? Do let me know what you have been doing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Generating Row Number Without Ordering Any Columns

Row_number function is used to generate a serial number for a given record set. But you need to always use ORDER BY clause so that the numbers are assigned to the specific order.

Let us create the following dataset

CREATE TABLE #TEST (NAMES VARCHAR(100))
INSERT INTO #TEST
SELECT 'PINAL' UNION ALL
SELECT 'MAHESH' UNION ALL
SELECT 'SUNIL' UNION ALL
SELECT 'ARVIND' UNION ALL
SELECT 'MURUGAN'

Suppose you want to generate row number, you can use the following statement

SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST

The reasult is

orderneeded1 SQL SERVER   Generating Row Number Without Ordering Any Columns
The numbers are assigned based on ascending order of name

But what if you want to generate row numbers in the same order the data are added.

Can you omit the ORDER BY Clause?

SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST

The above throws the following error

Msg 4112, Level 15, State 1, Line 1
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.

But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST

The result is

orderneeded2 SQL SERVER   Generating Row Number Without Ordering Any Columns
In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Who ALTER’ed My Database? Catch Them Via DDL Trigger

Here is an interesting comment from one of my previous blogs on how to change the compatibility level of SQL server post posted earlier. Some of these comments are interesting and can provide some interesting extension to blog ideas like this. So the comment was:

Under what circumstances would SQL Server automatically change the compatibility level ? I am currently working on SQL Server 2008 and there is a user database which has a compatibility level of 80. Apparently this database was moved to this server prior to my time, and the compatibility level was never changed/upgraded. When I execute the following script, it is set successfully to 100, however, the next day, it goes back to 80. Any ideas ?

USE [master]
GO
ALTER DATABASE [MyDB]
SET COMPATIBILITY_LEVEL = 100
GO

There is only a SQL Maintenance plan to perform integrity check, full database backups / transaction log backups, index rebuild/update stats on this instance.

Here is my reply:

SQL Server doesn’t do that automatically. There must be some automated activity doing it.

When the reader asked how to track such changes done in SQL Server. I had replied separately over mail and now sharing with you. I tried to write a script, which would create a DDL trigger on ALTER database command. Below script would do below things.

  1. Find who fired ALTER DATABASE Command.
  2. From which Application it was fired.
  3. What was the exact command fired.

Here is the code of the trigger.

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'DDL_Database_Trigger')
DROP TRIGGER DDL_Database_Trigger
ON ALL SERVER;
GO
CREATE TRIGGER DDL_Database_Trigger
ON ALL SERVER
FOR ALTER_DATABASE
AS
DECLARE
@cmd VARCHAR(200)
DECLARE @who VARCHAR(200)
DECLARE @fromwhere VARCHAR(200)
SELECT @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nVARCHAR(max)')
SELECT @who = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nVARCHAR(max)')
SELECT @fromwhere = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','nVARCHAR(max)')
RAISERROR (N'!!!Database ALTERED!!!
Command - %s
By - %s
From - %s.'
,
10,
1,
@cmd, @who, @fromwhere) WITH LOG
GO

For testing purpose, I fired below two commands:

USE [master]
USE [master]
GO
ALTER DATABASE [SQLAuthority] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [SQLAuthority]
ADD FILE
( NAME = N'MM',
FILENAME = N'E:\InvalidPath\MM.ndf', SIZE = 5120KB, FILEGROWTH = 1024KB)
TO FILEGROUP [PRIMARY]

And here is what I saw in ERROROG

ddl trigger 01 SQL SERVER   Who ALTERed My Database? Catch Them Via DDL Trigger

If you have not pre-configured DDL trigger to catch that information in ERRORLOG, don’t worry. You still have some information logged in default trace. Go through below blog where I explored Standard Reports which are available out of the box with SQL Server.

SQL SERVER – SSMS: Schema Change History Report

Have you ever used such tricks on your server? Do let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #018 – Script to Remove Special Characters – Script to Parse Alpha Numerics

If you ask me – there are certain questions not appropriate for an interview as they are often very subjective. There are some questions, which really do not help to identify if the candidate has the necessary skills or not. Here is one such question, if you ask me in the interview, I may not get it right too, as this requires some experience in writing scripts as well as trial and error mechanics to get it right.

Question: How to write will remove all the special characters and parses Alpha Numeric values only?

Answer: Honestly, it is not that easy to write this script. However, if you are stars are not bright you may end up with an interviewer who believes writing this script is the ultimate test. Do not get me wrong, writing this script is not a bad test, I believe it is not the best question for the interview.

CREATE FUNCTION dbo.UDF_ParseAlphaChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET
@string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET
@string = @string
RETURN @string
END
GO

You can test above function with the help of following test:

-- Test
SELECT dbo.UDF_ParseAlphaChars('AB"_I+{D[]}4|:e;"5,<.F>/?6')
GO

The above query will return following result set:

ABCID4e5F6

You can read about this function and additional comments in my earlier blog post over here: Function to Parse AlphaNumeric Characters from String

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Presented 3 Technical Session at Great Indian Developer Summit 2015

GIDS Website Logo SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015Great Indian Developer Summit is my favorite technology event and I have been extremely happy to present technology sessions here for over 5 years. Just like every year, this year, I presented three technology session on SQL Server 2014. This time the event was at two locations. First one is Bangalore and the second one is in Hyderabad.

If you did not attend the event, I suggest you sign up for my newsletter as I will be sending all the scripts, demos for this event in email over here. The event organizer is not planning to record the sessions.

GIDS%20(1) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

Performance in 60 Seconds – SQL Tricks Everybody MUST Know

Date and Time: APRIL 21, 2015 14:00-15:00
Location: J. N. Tata Auditorium, National Science Symposium Complex (NSSC), Sir C.V.Raman Avenue, Bangalore, India

Data and Database is a very important aspect of application development for businesses. Developers often come across situations where they face a slow server response, even though their hardware specifications are above par. This session is for all the Developers who want their server to perform at blazing fast speed, but want to invest very little time to make it happen. We will go over various database tricks which require absolutely no time to master and require practically no SQL coding at all. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally, all attendees of the session will have access to learning material presented in the session.

GIDS%20(2) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

Troubleshooting CPU Performance Issue for SQL Developers

Date and Time: APRIL 21, 2015 17:30-18:30
Location: J. N. Tata Auditorium, National Science Symposium Complex (NSSC), Sir C.V.Raman Avenue, Bangalore, India

Developers are in the most challenging situations when they see CPU running 100%. There are many solutions of this situation, but there is very little time to implement those solutions. In this critical situation developers need a sure solution which gives stability to their system and buys more time to troubleshoot the problem. Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. The truth is that art has evolved with the time and there are more tools and techniques to overcome ancient troublesome scenarios. There three major resource when bottlenecked creates performance problem: CPU, IO, and Memory. In this session we will focus on some of the common performance issues and their resolution. If time permits we will cover other performance related tips and tricks. At the end of the session attendee will have clear ideas and action items regarding what to do in when facing any of the above resource intensive scenarios.

GIDS%20(3) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

SQL Server 2014 – A 60 Minutes Crash Course

Date and Time: APRIL 25, 2015 09:55-10:55
Hyderabad Marriott Hotel & Convention Centre, Tank Bund Road, Opposite Hussain Sagar Lake, Hyderabad, India

Every new release of SQL Server brings a whole load of new features that an administrator can add to their arsenal of efficiency. SQL Server 2014 has introduced many new features. In this 60 minute session we will be learning quite a few of the new features of SQL Server 2014. Here is the glimpse of the features we will cover in this 60 minute session.

GIDS%20(4) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

  • Live plans for long running queries
  • Transaction durability and its impact on queries
  • New cardinality estimate for optimal performance
  • In-memory OLTP optimization for superior query performance
  • Resource governor and IO enhancements
  • Columnstore indexes and performance tuning
  • And many more tricks and tips

GIDS%20(5) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally, all attendees of the session will have access to learning material presented in the session. This one hour will be the most productive one hour for any developer who wants to quickly jump start with SQL Server 2014 and its new features.

GIDS%20(6) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

Remember – do not worry if you can’t attend the event. Just subscribe to newsletter and I will share all scripts and slides in the email right after the event.

GIDS%20(7) SQLAuthority News   Presented 3 Technical Session at Great Indian Developer Summit 2015

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL AZURE – How to Disable and Enable All Constraint for Table and Database

Recently I wrote blog to enable and disable all constraints in the database SQL SERVER – How to Disable and Enable All Constraint for Table and Database.

One of my reader told me that this trick of sp_msforeachtable doesn’t work in Azure SQL Database (WASD). It is interesting that a lot of things that we assume when working with on-premise SQL Server are not completely available when working on SQL Azure because it is Database as a service. Though some of the system commands are not exposed, we can always write something using the Dynamic Management Views (DMVs) something similar to achieve the same effect.

Here is the modified version of script which can be used on SQL Azure database. You can check the same on your servers and let me know.

-- Get ALTER TABLE Script for all tables to Disable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GO
-- Get ALTER TABLE Script for all tables to Enable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] NOCHECK CONSTRAINT all'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Note that Executing above would NOT make any change. By executing above script, there would be an output in Management Studio. This output is a set of commands that you will need to explicitly run it on the servers post that.

Let me know if you ever required such a capability when working with SQL Azure and what have you been doing to achieve the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Recovering from Snapshots – Notes from the Field #078

[Note from Pinal]: This is a 78th episode of Notes from the Fields series. What do you do when data is deleted accidentally or you are facing disaster? Well, there are plenty of the things, you can do, but when in panic quite often people make mistakes which just intensify the disaster. Database snapshot is very important but less frequently used feature.

JohnSterrett SQL SERVER   Recovering from Snapshots   Notes from the Field #078

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting subject of how to recover the database from snapshots. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the most common – and most forgotten – scenarios in disaster recovery plans is data being updated or deleted accidentally. This surprises me because, in the field, I see accidental data changes as a common disaster. If you have enterprise edition you can leverage database snapshots as a step in your disaster recovery plan to help recover faster, or at least allow you to pull back the majority of your data while you concurrently restore backups (to bring the rest of your data back). Restoring most of your data quickly can be critical with very large databases (VLDBs), especially if you have tight Recovery Time Objectives (RTOs).

Database snapshots require sparse files and store the original pages in these files when they are changed. Therefore, you will want to manage the sparse files and make sure you purge database snapshots as needed. You might also be using database snapshots without even knowing it. If you are doing database integrity checks with the default values, a special snapshot is taken in the background and your integrity checks are executed against that snapshot.

HOW DO WE CREATE A SNAPSHOT?

The following example uses the AdventureWorks2014 sample database. We need to use the CREATE DATABASE syntax, include the names of your data files, and include the file location of the sparse files. Finally, include AS SNAPSHOT OF database syntax to define the database as a snapshot.

CREATE DATABASE [AW2014_Snapshot_morning] ON
( NAME = AdventureWorks2014_Data, FILENAME =
'C:\Demo\AW_data.ss')
AS SNAPSHOT OF AdventureWorks2014

REAL-WORLD ACCIDENTAL DATA CHANGE STATEMENT

Here is a common case of a mistaken DELETE statement. We have a DELETE statement with the primary key included to delete a single row. By mistake we didn’t highlight the filter so all the rows will be deleted.

If you execute this statement, you will see an unexpected result: we deleted all rows in the table as shown below.

notd 78 SQL SERVER   Recovering from Snapshots   Notes from the Field #078

(19972 row(s) affected)

HOW DO WE REVERT FROM SNAPSHOT?

You have two options if you created a database snapshot earlier.

First, you could insert the data back from your snapshot database as shown below. This could be done with SSIS, BCP or many other tools. For this quick example we will do this with an INSERT INTO SELECT statement.

SET IDENTITY_INSERT Person.EmailAddress ON
INSERT INTO
Person.EmailAddress (BusinessEntityID, EmailAddressID, EmailAddress, rowguid, ModifiedDate)
SELECT *
FROM AW2014_Snapshot_morning.Person.EmailAddress
SET IDENTITY_INSERT Person.EmailAddress OFF

Second, you can revert the database from the snapshot. Keep in mind this second option will revert all data changes in the database not just the data deleted in your accidental data change statement.

USE MASTER;
RESTORE DATABASE AdventureWorks2014 FROM
DATABASE_SNAPSHOT = 'AW2014_Snapshot_morning';
GO

From these examples, you can see database snapshots are a tool to help you recover data quickly. Please note that you wouldn’t want database snapshots to be your sole plan for disaster recovery and unplanned data change statements. If your snapshot or the original database suffers corruption, you wouldn’t be able to recover. So make sure you add snapshots into your existing disaster recovery plans, which should – at a minimum – include database backups to give you a better recovery point objective.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding Tables with Primary or Foreign Keys

If you want to know if a table has a primary key or foreign key, you can find out this in many ways.

Let us create these tables

CREATE TABLE product_master
(
prod_id INT PRIMARY KEY,
prod_name VARCHAR(100),
price DECIMAL(12,2)
)
GO
CREATE TABLE product_details
(
prod_id INT,
sales_date DATETIME,
sales_qty INT,
sales_amount DECIMAL(16,2)
)
GO
CREATE TABLE company_master
(
compnay_id INT,
company_name VARCHAR(100),
address VARCHAR(1000)
)
GO

Now let us create foreign key:

ALTER TABLE product_details ADD CONSTRAINT ck_item FOREIGN KEY(prod_id) REFERENCES product_master(prod_id)

Now if you want to check if a table has a primary key, you can use the following methods

1) Use sp_pkeys system stored procedure

EXEC sp_PKEYS product_master

The result is

objprop1 SQL SERVER   Finding Tables with Primary or Foreign Keys

2) Use Objectproperty function

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

objprop2 SQL SERVER   Finding Tables with Primary or Foreign Keys
if you want to check if a table has a foreign key, you can use the following method

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

The result is

objprop3 SQL SERVER   Finding Tables with Primary or Foreign Keys

If you want to check for the tables that do not have primary key or foreign key, you can use the following method
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=0 AND
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=0 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

The result is
objprop4 SQL SERVER   Finding Tables with Primary or Foreign Keys

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script: Knowing Data and Log Files Are On the Same Drive

Last week it was wonderful to spend time at the GIDS conference to meet all the enthusiastic developers. Had fun preparing for the session and the number of people who walked to me and say they read this blog makes me really humble and motivated to write more. I love attending and presenting at these conferences because I get an opportunity to meet people and at many times able to answer real world problems.

During one of my sessions on performance, I mentioned about the need to keep the data files and log files on different drives. I showed the impact of placing log and/or data files on a slower drive and we had loads of fun learning. After the session, one of the attendees walked up and asked a simple question. He said he had 100’s of databases running in their environment. He wanted to know which of these databases were having both the data files and log files on the same drive.

My first instinct was to search the blog to see if I had already written about it but to my surprise it was not. So with a little bit of query to DMVs and metadata tables, here is what I came up with.

SELECT SERVERPROPERTY('machinename') AS 'Server Name',
ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename'))  AS 'Instance Name',
name,
drive_letter AS 'Drive Letter',
Comments, Path
FROM
(
(
SELECT DISTINCT
UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
N'Device holds both tempdb and user database objects' AS 'Comments',
DB_NAME(database_id) [name],
1 AS OrderBy
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) = 'tempdb'
AND UPPER(LEFT(LTRIM(physical_name),2)) IN
(
SELECT UPPER(LEFT(LTRIM(physical_name),2))
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'tempdb', N'master', N'msdb', N'adventureworks', N'adventureworksdw', N'model')
)
)
UNION
(
SELECT drive_letter, path,
N'Device holds both data and log objects' AS 'Comments', name,
2 AS OrderBy
FROM
(
SELECT drive_letter, name, Path
FROM
(
SELECT DISTINCT UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
TYPE, DB_NAME(database_id) [name]
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'master', N'msdb', N'tempdb', N'adventureworks', N'adventureworksdw', N'model')
)
a
GROUP BY drive_letter, a.name, path
HAVING COUNT(1) >= 2
) Drives
)
)
Drive
ORDER BY OrderBy, drive_letter

On my dev box it looks like this:

data log same drive 01 SQL SERVER   Script: Knowing Data and Log Files Are On the Same Drive

I am sure if you run the script, it will show something similar to this. Do let me know if you ever needed such a script and were not able to get the same? I would love to know, are there some simple daily scripts that will make your life easy and you don’t have them handy? Let me know, we will try to make them into the blog somehow.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Collect and Analyze SQL Server Data Efficiently

dbForge Event Profiler is one of the most useful SQL Server “build-in” tools. The Profiler records data associated with various SQL Server events for further analysis. This data is stored in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose SQL Server relates problems. The tool allows you to view communications between a client and SQL Server, and gives you an insight into its internal performance. To take full advantage of its potential, download dbForge Event Profiler for SQL Server for free now.

The tool offers a large spectrum of features that can assist you in:

  • analyzing trace results by grouping or aggregating them;
  • auditing user activity;
  • creating your own custom traces and save them for future use;
  • debugging T-SQL code and stored procedures;
  • executing quality assurance check;
  • identifying performance-related problems with front-end applications, queries, T-SQL, transactions, and so forth;
  • performing stress testing;
  • performing query analysis of execution plans;
  • viewing SQL Server performance when interacting with a client.

Essentially, the Event Profiler is designed to quickly and efficiently track down and fix many SQL Server related problems, such as poorly-performing queries, locking and blocking, excessive table/index scanning, and a lot more. For example, you can monitor the execution of a stored procedure to see whether it hampers SQL Server performance.

Using the Profiler, you can monitor the events that you are interested in. For example, you may want to capture events from a specific user or a given database. Smart filters allow you to collect only the events that you want, filtering out those of no interest. This reduces the amount of data that is stored in your trace.

dbForge Event Profiler provides a rich graphical user interface that can be used to create, analyze, and replay trace results. As the trace data is being collected, you can stop or pause the trace at a certain point and store the trace results to a physical file on a local hard disc. The saved SQL Server Profiler document has the “.*ssp” extension. This file may then be viewed to analyze data captured, share it with others, or compare the trace results to traces performed later.

Powerful Tool in Action

Below is an example of how you create a new trace using dbForge Event Profiler.

To create a new trace, you follow these steps:

  1. On the Start page, click Profile Server Events. The Profile Server Events wizard appears.
  2. Specify the connection.
  3. Optionally, select a profiling template, modify the trace file settings and the data storage settings. Click Next.

devarteventprofiler1 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

  1. On the Events to Capture page, select the events you want to capture. Click Next.
  2. Optionally, on the Actions page, select the actions you want to capture in the current events session. Click Next.
  3. Optionally, on the Event Filters page, specify filter options to limit the tracing data.
  4. Click Execute. The Trace will start and the Server Event Profiler Document opens.

devarteventprofiler2 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

Once you have collected enough data, stop the trace by clicking the Stop Trace button on the toolbar. At this point, you may review the collected data or save it to a file for future use.

Now that you have learned about many advantages of this smart tool, you can start mastering and making practical use of the dbForge Event Profiler for SQL Server by downloading it now for FREE.

Reference: Pinal Dave (http://blog.sqlauthority.com)