How to enable and configure interactive (dynamic) sorting on your SSRS reports.

In reporting server you can build reports which are sorted in a specific way. However sometimes it depends on the usage of the report on which column a report should be sorted. User A want to sort it on column X and user B wants to sort it on column Y. To configure dynamic sorting on your SSRS reports you need to do the following:

1. Open your SSRS report.
2. Select the header of the column on which you want to enable interactive sorting.
3. Open the properties of the Text properties window.
4. Tick enable interactive sorting on this text box.
5. Select the columns of the dataset you want to sort on.
6. Repeat previous steps for all columns on which you want enable interactive sorting.See the red circles for which columns interactive sorting is enabled.

Enjoy building nice reports.

Use SQL profiler replay traces to benchmark performance of your SQL server database.

Photo credit: Mike Johnson - TheBusyBrain.com

In SQL Server 2008 (R2) a lot of new features are build to improve the overall performance of your database. These features are mostly independent of your application. To implement these new features, you want to know what the impact is of an implemented feature. This blogpost will explain how SQL Profiler can help you to simulate 'production' workload on your database.
Some examples of these new features:

• Filtered indexes
• Data compression
• Optimize for ad-hoc workloads.
• Partitioned tables in combination with data compression

The biggest challenge in simulating workload is to get a workload which is comparable to the workload in the production environment. This is possible with SQL Profiler. With SQL profiler you can capture the workload on your production database. This capture can be used as workload on a copy of your production database.

In general the replay process can divided into:

1. Make a full backup of the production database.
2. Capture workload with SQL Profiler.
3. Restore production database on a test server.
4. Replay workload to create baseline. Use SQL profiler to measure CPU and IO
5. Restore production database and configure some of the new features.
6. Replay workload. Use SQL Profiler to measure CPU and IO.
7. Compare the results with the baseline.

Step 1: Make a full backup of your production database.

Step 2: Capture workload with SQL profiler

1. Start SQL Profiler with the trace template 'TSQL_Replay' and a filter on the database id of your production database.
2. Save the trace results to a new database on another SQL server as your production server.
   
   
   
   
3. Select a database in which you want to store your replytraces and define a table name. In this example I have created a Database: ReplayTraces.
   
   
   
    
4. Define a filter for the database of your production database. Use next query to retrieve the database ID of your production database:
   SELECT Dbid FROM Master..SYSDatabases WHERE Name = ''
5. Start the profiler when the FULL BACKUP process is almost completed. Starting the replay just before the full backup is completed garantees that you have all queries which are executed after the full backup is completed.
6. The profiler will now capture all executed queries on your production database.
7. Stop the SQL Profiler trace at the moment you have captured enough data which can be representative for your tests.

Step 3: Restore production database on a test server.

Now we have a backup of the production database and a database with the captured workload. Be sure to have backups of these 2 database because you will need them a lot of times for your tests.
Restore the backup of your production database on your test server.


Step 4: Replay workload to create baseline. Use SQL profiler to measure CPU and IO

For a benchmark we need to have a baseline. To create a baseline execute next steps:

1. Load the captured profile data in the SQL Profiler.
2. Open SQL profiler and select File, Open, Trace Table.
3. Select the SQL Server,Database and tablename in which you have captured the production workload.

SQL Profiler Replay Requirements:
To replay a trace against a server (the target) on which SQL Server is running other than the server originally traced (the source), make sure the following has been done:

• All logins and users contained in the trace must be created already on the target and in the same database as the source.
• All logins and users in the target must have the same permissions they had in the source.
• All login passwords must be the same as those of the user that executes the replay.
• The database IDs on the target should be the same as those on the source. If they are not the same you can do the following: Assume Source DBID = 10 Target DBID = 6. Detach your TestProduction database. Create a new database. This database will get DBID 6. Create 3 other Databases. The last created database will have DBID 9. Attach you TestProduction database. This will now get DBID 10.
• The default database for each login contained in the trace must be set (on the target) to the respective target database of the login. For example, the trace to be replayed contains activity for the login, Fred, in the database Fred_Db on the source. Therefore, on the target, the default database for the login, Fred, must be set to the database that matches Fred_Db (even if the database name is different). To set the default database of the login, use the sp_defaultdb system stored procedure.

More information about replay requirements can be found here

1. Create and start a SQL profile trace with a filter on the database ID of the restored production database on the test server. Save the results to a SQL Server database. This will be your baseline.
2. To start the Replay, press the yellow arrow.

Step 5: Restore production database and configure some of the new features.
In the previous step we made the baseline. Now it is time to test the new features.

1. Configure the new features you want to test.
2. Load the captured profile data in the SQL Profiler
3. Create and start a SQL profile trace with a filter on the database ID of the restored production database on the test server. Save the results to a SQL Server database in another table as you used for your baseline.
4. Start the replay.

Step 6: Replay workload. Use SQL Profiler to measure CPU and IO.

Step 7: Compare the results with the baseline.

The results of the baseline and the first test are stored in 2 seperate tables. For instance: Table Baseline and Table Test1_Datacompression.

Use next query to compare the results:

SELECT 'Baseline' AS Test, COUNT(*) AS Queries,
    SUM(CPU) AS CPU,SUM(READS) AS Reads,
    SUM(Writes) AS Writes,SUM(Duration)/1000 AS Duration
FROM EOLSQL2008Replayresults.dbo.Baseline
WHERE EVENTCLASS in (10,12)
UNION ALL
SELECT 'Test1_Datacompression' AS Test, COUNT(*) AS Queries,
   SUM(CPU) AS CPU, SUM(READS) AS Reads,
   SUM(Writes) AS Writes, SUM(Duration)/1000 AS Duration
FROM EOLSQL2005Replayresults.dbo.Test1_Datacompression
WHERE EVENTCLASS in (10,12)

The number of queries should be the same because you replayed the same workload on both databases.

Success with your benchmark.

Forced parameterization does not work for partly parameterized queries.

In my previous blog I described how you can recognize a forced parameterized query. If you have set the parameterization option to forced on database level, you can still find some queries which are not parameterized. In this blog I will describe why?

Please use the AdventureWorks database to use the scripts.

USE AdventureWorks


--Enable the forced parameterization on the database
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
GO
-- Clear the procedure cache
DBCC FREEPROCCACHE
GO
-- Update 2 different records both with an different Title.
UPDATE HumanResources.Employee SET Title = 'Support' WHERE ContactID = 1002
GO
UPDATE HumanResources.Employee SET Title = 'xx1290Support' WHERE ContactID = 1290
GO
-- Look in the procedure cache.

SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 1 entry Title and ContactID are parameterized.


-- Clear procedure cache
DBCC FREEPROCCACHE
Same query but ContactId is parameterized. Title is not parameterized.
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = ''Support'' WHERE ContactID = @P1' ,N'@P1 INT',@P1 = 1002
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = ''xx1290Support'' WHERE ContactID = @P1' ,N'@P1 INT',@P1 = 1290
GO
-- Look in the procedure cache
SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 2 entries. Forced parameterization does not work.
-- Clear procedure cache
DBCC FREEPROCCACHE

-- Same query ContactID and Title are parameterized.
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = @P2 WHERE ContactID = @P1' ,N'@P1 INT, @P2 varchar(20)',@P1 = 1002, @P2 = 'Support'
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = @P2 WHERE ContactID = @P1' ,N'@P1 INT, @P2 varchar(20)',@P1 = 1290, @P2 = 'xx1290Support'
GO

-- Look in the procedure cache
SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 1 entry because the query is already parameterized.

Conclusion: Forced parameterization does only work if the query does not contain any parameter. If at least one parameter is defined, forced parametization does not work anymore.

Enjoy the use of forced parameterization in the right context.

How to recognize forced parameterization queries in the procedure cache?

At the moment a query is started SQL Server will first analyze the query to find the best way to execute the query. This is called the execution plan. In the execution plan information is stored about for instance which index to use to retrieve the data. All execution plans are stored in the plan cache. If a new query is executed, SQL server will look if an execution plan is available for this query in the plan cache. If it is available it will be re-used. This will save the generation of the execution plan. For every execution plan SQL server counts how much time it is used. In your application you can parameterize your queries so SQL can re -use query plans. In case your application is not using parameterization, SQL Server can force parameterization on a database level. Analyzing the procedure cache can help you in optimizing the performance of your application. This blogpost will explain how you can recognize forced parameterized queries in the procedure cache.

Next example queries can be used on the AdventureWorks demo database. To download the AdventureWorks demo database click here.

First of all we will not use force paramerization:
ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

Execute next example of a parameterized query:
exec sp_executeSql N'Select * from HumanResources.Employee where ContactID = @P1' ,N'@P1 INT',@P1 = 1290
GO

Execute the same parameterized query for another value (1002)
exec sp_executeSql N'Select * from HumanResources.Employee where ContactID = @P1' ,N'@P1 INT',@P1 = 1002
GO

Now we can look in the procedure cache:
SELECT text,execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text

You will get one row with an execution count of 2. Because the query uses a parameter for ContactID, the execution plan is available when the query for parameter value 1002 is executed.

The parameterized query can be recognized on the ContactID=@P1 (Remember the P)

If we do not use parameterization we have next 2 queries:

SELECT * FROM HumanResources.Employee WHERE ContactID = 1290
GO
SELECT * FROM HumanResources.Employee WHERE ContactID = 1002

The procedure cache will have 2 entries. One for every query.

Now we enable forced parameterization.

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
Execute a non parameterized queries:
SELECT * FROM HumanResources.Employee WHERE ContactID = 1290

go
SELECT * FROM HumanResources.Employee WHERE ContactID = 1002

The procedure cache will have one result set:

The forced parameterization can be recognized on the
ContactID = @0. (So without the P)

Enjoy analyzing your procedure cache to further improve the performance of your application.

Photo Credit: Suchitra Prints

Whitepaper: High Performance SQL Server Workloads on Hyper -V

As of today, virtualization is used in a lot of server rooms. It started with servers like web servers, domain controllers, ftp servers etc. People are getting now more and more experiences with virtualization. Virtualization is getting a common used technology. To deploy SQL Server to a virtualization environment you need to take more things into account. Microsoft has made a whitepaper how you can deploy Microsoft SQL Server 2008 to a virtualization environment using Microsoft Windows Server 2008 R2 Hyper-V. It demonstrates that Hyper-V provides the performance and scalability needed to run complex SQL Server workloads in certain scenarios. It also shows how Hyper-V can improve performance when used in conjunction with advanced processor technologies. This paper assumes that the reader has a working knowledge of virtualization, Windows Server Hyper-V, SQL Server, Microsoft System Center concepts and features.

Enjoy reading: Whitepaper: High Performance SQL Server Workloads on Hyper -V