DevJef's Mumbo-Jumbo

«There's a bit of SQL in all of us»


News

 

In order to test Performance in SQL Server, normally you would take a look at how may I/O or how many cycles a statement takes to complete. To make it less complex, you can take a look at how long a statements takes, just by looking at the execution-time. In most cases, network latency, SQL Server hardware and workstation performance are also included in this result (time to create result set, and time needed to send across the internet or internal network). And if a statement takes les then a second to execute, you need to perform some magic with dates, times, etc. It is what it sounds like: overkill. There is a simple way to test performance in SQL Server.

 

In order to understand the statement, first a small explanation of the keywords:

 

@@CPU_BUSY: Returns the ticks “spend” since the last execution of the statement

ISNULL: Replaces NULL with the specified replacement value

COALESCE: Returns the first non-NULL value from it’s replacement values

 

So basically ISNULL and COALESCE provide you with the same functionality( at least, the way I used it in the script below), except COALESCE can be used to prevent the inevitable bunch of nested ISNULL statements.

 

To test the statements, I used the following script:

 

  
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY DECLARE @StartDate DATETIME SET @StartDate = GETDATE() WHILE @Loops <= 1000000
BEGIN
    IF COALESCE('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END PRINT 'COALESCE, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO --================================================== DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE() WHILE @Loops <= 1000000 BEGIN
    IF ISNULL('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END PRINT 'ISNULL, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

If you execute this statement, you will see the following results:

 

COALESCE, both non-NULL
Total CPU time: 16
Total milliseconds: 566
 
ISNULL, both non-NULL
Total CPU time: 19
Total milliseconds: 640

 

As you can see, the COALESCE takes less time to complete then the ISNULL function. The total CPU time needed is shorter, and looking at the used time, it saves you 74 milliseconds. In this case it isn’t that much, but with larger queries this can save you a lot of time.

 

The actual CPU time and milliseconds vary because of different hardware, caching, etc. But the outcome is the most important.

 

For those people who want to comment about COALESCE; I know! COALESCE is not exactly the same as ISNULL, but like I said, in this script it is!


 

How fun is it to use new software? As a developer you always want to use the latest software, and in many cases it’s backwards compatible. This (unfortunately) isn’t the case with Report Builder 3.0 and SQL Server 2005 Reporting Services. This is a “by design” issue, but it’s such an annoying “feature”. If you try to deploy a Report built in RB 3.0, you will get the error:

 

"The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded. (rsInvalidReportDefinition)"

 

In most cases you will copy-paste the tables or objects from RB 3.0 to the Business Intelligence Development Studio (BIDS), and save it again. But sometimes you just want to take the shortcut, and that is possible as well. As always, I have to mention that I don’t guarantee anything, but it worked for me several times.

 

The first step is to open the .RDL file with a text editor, and replace the following line of XML:

 

<Report xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner” 
xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition“>

 

with:

 

<Report xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner”
xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition“>

 

Step two is removing a set of nodes in the RDL, that is not supported by versions older then Report Server 2008:

 

<ReportSection>     <ReportSections>

 

 

And it’s closing tags:

 

    </ReportSection>
</ReportSections>

 

Don’t remove the text inside these tags, only remove the tags themselves!!!

 

If you follow these steps, you will be able to deploy a “new” Report to an “old” Report Server without weird copy-paste actions.


 

How often does it happen: you write an application that uses a SQL Database as source, and when you want to fetch data you get a time-out. Damn! But when you need to get a time-out (for example, if you want to test an exception), you don’t get one. In SQL Server you can create your own time-outs if you want.

 

One of the simplest ways to generate a time-out, is to lock an object, and running a query against the object in another session. You can lock an object with a simple query:

 

SELECT *
FROM Company WITH(TABLOCKX, HOLDLOCK)

 

The query hint TABLOCKX puts an Exclusive lock on the table, and the HOLDLOCK hint creates a more exclusive Shared Lock, but this will be hold until the end of your transaction.

 

This will be obsolete if your query will retrieve 10 records for example, because this will not hold a lock long enough to cause a time-out. You can hold a lock longer by using the WAITFOR hint:

 

BEGIN TRANSACTION

SELECT * FROM Company WITH(TABLOCKX, HOLDLOCK)

WHERE 0 = 1

WAITFOR DELAY '00:00:05'

ROLLBACK TRANSACTION

 

This query uses the same hints as the first query, with the addition with the WAITFOR DELAY. This will instruct SQL Server to wait for a specified time or time interval, before executing the ROLLBACK TRANSATION.

 

The WAITFOR can be used in two ways:

 

WAITFOR DELAY ‘00:00:05’

This will instruct SQL to wait for five seconds.

or:

WAITFOR TIME ‘12:00:05’

This will instruct SQL to wait untill 5 seconds past 12:00, to execute the ROLLBACK.

 

With these statements it’s possible to test use Locks and test time-outs without killing a SQL Server. There is only one more thing I need to tell you:

 

 

USE THE FORCE WISELY! ONLY FOR GOOD, AND NOT FOR EVIL!!! :)


 

A colleague asked me if it’s possible to set the parameters of a Report in the URL, so that he could send it to an end-user. I didn’t know the answer, and after some research I came to the conclusion that it’s possible.

 

Just like your used to, you pass parameters into the Report by modifying the URL. An example of a normal Report URL looks like this:

http://<ServerName>/Reports

 

In order to use parameters in the URL, you need to use the Service URL. The Service URL has the possibility to add specific options into the URL, so that SSRS knows what to execute. The changed URL looks like this:

See Reports directory:

http://<ServerName>/ReportServer

List Reports in SSRS directory:

http://<ServerName>/ReportServer?%2fArea+51&rs:Command=ListChildren

 

As you can see, the SSRS contains a folder called “Area 51”, and a subfolder “Finance”. This folder contains the Report “CustomerReport”, which we want to use to pass parameters.

 

Parameters in an URL are added as followed:

Just a CustomerID:

http://<ServerName>/ReportServer/Pages/ReportViewer.aspx?%2fArea+51%2fFinance%2fCustomerReport&CustomerID=1&rs:Command=Render

And CustomerID and PaymentDate:

http://<ServerName>/ReportServer/Pages/ReportViewer.aspx?%2fArea+51%2fFinance%2fCustomerReport&CustomerID=1&PaymentDate=2011-09-23&rs:Command=Render

 

The “rs:” parameter tells the SSRS what to do with the URL. “ListChildren” will create an open-dir look and feel, and “Command=Render” will actually execute and render the Report.

 

There are a lot more properties that you can use to modify the URL, and give the end-user a Report with specific data, without storing default values in the Report itself.

 

Sources:

MSDN


 

How many times do you wonder about when an object was last updated? You can find out by using the default sysobjects in SQL Server:

 

USE Adventureworks


DECLARE @DBID INT = DB_ID('Adventureworks')


SELECT
	o.name AS TableName,
	ddius.*
FROM sys.dm_db_index_usage_stats ddius
JOIN sys.objects o
	ON o.object_id = ddius.object_id
WHERE database_id = @DBID
ORDER BY ddius.last_user_update DESC

 

This will give you the last_user_update, which is based on indexes. The Insert, Delete or Update are stored in the sys properties of the database. So this only works if there is an index of some sort is used on the object!


 

This blog will be used as a collection of scripts, experiences and adventures in my weird travels around Development-Land:

  • Blog? Check!
  • Scripts? Check!
  • Stuff to blog about? Check!

Preflight check completed!