Quick T-SQL Tip #2: Identity Columns Seed Value

This post really deals with high volume tables with an identity column, but the idea is something that everyone should at least be familiar with.

Quite often I see table creation scripts that start like this:

Create Table ATable
(
ID int not null identity(1,1)
...

And while that is perfectly legal and will work with no issues, if this table is going to be a high volume table, you should consider using the entire Int, not just half of one.

What??

The Int data type spans -2,147,483,648 thru 2,147,483,647.  If your seed is 1, you lose 2,147,483,649 values that could be used.

I have seen production situations where they have run out of identity values because the seed was 1.
So, next time you create an Identity, ask yourself: "Two billion, or four billion?"

and then 

Create Table ATable
(
ID int not null identity(-2147483648,1)
...

peace

Quick T-SQL Tip #1 - Formatting SQL

I am going to start a series of posts for quick T-SQL tips.  In my current position, I support a group of business report developers that are not too familiar with T-SQL.  This will help them learn some of the tips and tricks that I use frequently.

So, tip #1, Formatting SQL.

This tip does not just apply to T-SQL, but any kind of code in general.
I am sure most other developers have been here:  You inherit some code and the first hour is spent trying to figure out what it's doing!

Well, here is my basic formatting.  My goal is to let anyone look at it and understand it quickly!


Select tab Field1 As Field1Name,
tab tab Field2 as Field2Name
From tab Table1 T1
Inner join
tab tab Table2 T2
on tab tab t1.Field = T2.Field
Where tab T1.Field = 'AA'
Order by
tab tab T2.Field1 ASC

Doesn't look that pretty there, but in SQL Management Studio, it works like a charm!  :)

My goal is to have enough whitespace that the SQL does not look cluttered.
No matter how you format, always keep the next developer in mind!!

peace

Error Calling Stored Procedure with DATE and TIME datatypes from SSIS

When calling a stored procedure using an OLE DB Command from SSIS to a stored procedure that has a DATE or TIME parameter, you will get the following error:

An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Operand type clash: int is incompatible with date"

The quick fix to this is to change the parameter type in the stored procedure to a varchar(50).
Some blogs recommend changing it to DATETIME, but the range of DATETIME is much smaller than DATE and will truncate.

peace

SQL Server Task Status

Have you ever been performing a DBCC SHRINKFILE and wondered how long it was going to take?  The Backup/Restore screen shows percent complete, how about DBCC CHECKDB?

Here's a neat query that can help with that:

Select * from sys.dm_exec_requests

This query returns information about every request that the SQL Server is executing.

One of the columns returned is Percent_Complete.  However, this column is only populated for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

I have four large databases that I have to shrink when they are copied to the development.  These can take quite a while, so I use this query to monitor status.

Check out the documentation for more details!

peace

Performance Tuning and Cache

When tuning queries and indexes for performance, it's a good idea to clear the cache when running benchmarks.
For example, when you first run a query, a plan is created, and then data is fetched from the drive.
On the next execute, your query plan won't need to be created, and the results will probably come from the cache, giving a false indication of how long the query will run.

So to clear the cache, execute these two statements:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Then you can test with each execution being treated equally.

Take care on the FreeProcCache, as this is a server level command.   Do not run this on a production box unless you really know what you are doing!

peace

SQL Server 2008 R2 and Hyperthreading

So I have read about the debate about hyperthreading on a SQL Server or not.

Well, I decided to put it to the test.

I have an ETL process that runs about 8 hours, moving data from a DB2 source, to a staging area on SQL Server.
Then it is moved into an operational data store, and then finally into a data mart.

The system has four 6-core Xeon processors.

I turned off hyperthreading, and the ETL process decreased by more than an hour!

So there it is.. My totally unscientific, unique results!

Your mileage may vary, but at least give it a try on a Xeon processor.

peace

SSRS Reporting in Java

So we are in the final testing stages of our SSRS reporting interface in a Java web front-end.

The results are really fantastic! However, going thru this process has taught me a lot about how SSRS works internally, things that I took for granted when using report manager to render reports. These things will probably help my report design in the future, so I thought I would write them down and pay it forward!

First, a bit about how we implemented this:

In our reports, we force exact pagination by having a caption at the top of the report that reads:  This report contains xxx pages.  Since the report wants this number, SSRS will go ahead and process the report to calculate the page count.  If we had left this out, SSRS would render the first page and not worry about how many subsequent pages were coming.

In the front end, we render page 1 of the report, get the total page count, and then display the first page in a viewer similar to report manager. Note that we are NOT using the report viewer control since this is a Java application. We render all pages to HTML4.0 and display as is on the web page. Since SSRS embeds all of the formatting, the front end is responsible for none of that.

When the user clicks to go to the next page, we again call render with the page number in the device info XML.

In parallel, I created a small C# app that calls a report and renders it much the same way.  I did this so I could make sure I fully understood the process and could assist the Java developers.

We ran into issues when our reports took much longer to render in our front end than it did in report manager or my C# app.  So I started watching stats using the ExecutionLog3 view on the report database.  I paid special attention to TimeDataRetrieval, TimeProcessing, and TimeRendering.

From my app I was seeing the data retrieval occurring on the first render, and some time processing and then some time rendering.  These numbers were all pretty much in-line with what I would have expected. For a 110 page report, I had 3000-3500ms for data, 750-1000ms for processing, and 1000-1500 for rendering. On this first page, the Source field was Live.

On moving to the next page though, the Source field was Session, I was seeing no data retrieval at all, and minimal processing and rendering: 0ms for data, 35-40ms for processing, and 100ms for rendering. Very acceptable numbers for a reporting platform! So this tells me that SSRS processes the dataset all at once, does a bit of pre-rendering to get the page count, and fully renders the first page.

From our front end however, I saw something completely different! Every request had a Source of Live, and each request was pulling data and rendering, sometimes taking up to 15-20 seconds to run the report.

I put WireShark on the SSRS server and started watching network packets. I was able to watch every command that came into the SSRS server, and the response from the server.  It quickly became apparent what was happening.  The front end was not sending the page number for the first Render call, and then calling SetExecutionParameters between each page. The first Render call was causing the server to fully render the report for the first page. The SetExecutionParameters call was invalidating the session and causing the report to be full re-rendered, including data retrieval, for each page. Obviously this was the cause behind the huge difference in execution times!

So I worked with the Java developer and was able to remove any extraneous SSRS calls, and now the performance is amazing!

Our report template it pretty complex, we have a lot of background features that require processing, so to have execution times in the low 100ms range is great!

Speaking of our report template, we have a parameter called Date Range.  A user can select a value in this drop down and select ranges such as last month, last year, etc. When the user selects one of these, we call the SQL Server to return a start and end date based on the selection. In report manager, once you select one date range, and then try to select a second, the start and end dates will not update.  This is a “feature” according to Microsoft. Since we wrote our own front end, we were able to work around this. So you can select Last 2 Years and the start and end dates will populate, and then you can change to rolling 13 months and the start and end dates will update accordingly!

That’s all for now… I welcome any and all questions…

peace

SSRS Create Subscription Issue Using Web Services

Our implementation of SSRS into our Java web site is going very well! However, we ran into an issue trying to set up subscriptions thru the web services.

When trying to set the message body text (the Comment field), we got the error:

One of the extension parameters is not valid for the following reason: you do not have permission to modify the value for the "Comment" 

The users all have the browser role, which allows Manage Individual Subscriptions, but this is not enough. There is a restriction in that setting that disallows the user from setting the comment field.
What I had to do was give the browser role the ability to Manage All Subscriptions. The major caveat with this setting is that if the application calls ListSubscriptions(null, null), the user will see all subscriptions in the system. We had to setup code on the front end to ALWAYS pass the UserID to the ListSubscription method. (http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.listsubscriptions)

Also, note that for the My Reports folder, the browser security role is not used, its the My Reports role. You will need to give the My Reports role the Manage All Subscriptions task as well.

peace
BobP

SSRS Subscriptions and Blank Labels

Ran into an interesting issue with SSRS subscriptions. Actually, I am very surprised I have not run into this yet.

I am running SQL Server 2008r2 SP1.

If you have a report with a multi select parameter that allows blanks, a blank label will either prevent you from setting up the subscription, or cause the subscription to fail when it is run.

The Javascript error is:

Message: 'firstChild' is null or not an object
Line: 640
Char: 13
Code: 0
URI: http://Server/InternalReporting/js/ReportingServices.js

and the subscription error is:

Failure sending mail: Default value or value provided for the report parameter 'ReportParameter1' is not a valid value. Mail will not be resent.

I have opened an issue on Connect:  http://connect.microsoft.com/SQLServer/feedback/details/750927/blank-parameter-labels-cause-error-in-subscriptions

What I have done in the meantime is to replace any blank labels with a holder character ("-").

Has anyone else ran into this issue?

BobP

SSRS Parameters are Forced Unicode

While doing some performance tuning on a report query today, I ran into the issue of SSRS sending all strings to a SQL Server database as unicode. Here is a snippet of the query I was working with:

or tbl.Field in (N''0100'',N''1002'',N''1201'',N''1202''....

This snippet came right out of profiler with no editing...

The N in front of each string forces SQL Server to treat it as unicode, as an NVARCHAR string. The issue with this is that my tbl.Field column is a varchar. So no indexes would ever be used for this query, it was always a clustered index scan.

My workaround was to add an nvarchar column, using a trigger to popuate it with the nvarchar version of the varchar field. Messy, but once I did that, and indexed it, the query performed fine.

I have opened up a Connect suggestion, so please visit that and vote for it. This situation should not exist.

https://connect.microsoft.com/SQLServer/feedback/details/740975/ssrs-sends-all-strings-to-sql-server-as-unicode

peace

SSRS Reporting in a Java Web Site

For the last 6 months or so, I have been working on a project with our web application development team to integrate SSRS reporting into our client facing Java web site.

When I first proposed this project, I knew that in theory this should work fine. But, it did take a bit of research and a lot of work to bring it to fruition.

We are moving into the testing phase this week, and I am happy to say that it works very well! The Java developer working on this is doing a great job of translating all of my Microsoft talk to java :)

We are using the Web Services provided by SSRS with a custom security extension that authenticates against our web site security.

Since we are only rendering one page at a time, rendering is pretty quick, taking less than a second a page to render.

Once we go thru testing and release, I am going to write more on this, including some details.

Retrieve Table List in DB2/400 via SQL Statement

Again, mainly to remind myself, the query to retrieve a list of tables in DB2/400 is:

select * from qsys2.systables where table_type='T';

If you want a list of views, table_type would = 'V'

peace

Duration Change in SQL Profiler 2005

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds. - Microsoft Books Online

This is just a helpful tip so you don't waste time following a non-issue... :-)

peace

Multi Row Inserts in T-SQL

A new feature in SQL Server 2008 is the ability to insert multiple rows with one insert statement!

In previous versions, anytime you wanted to insert multiple rows into a table with a T-SQL script, you would have done something like this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123)
Insert into MyTable (Field1, Field2)
Values ('DEF',456)

However, with SQL Server 2008, you can now do this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123),('DEF',456)

Working with a growing data mart, I do a lot of scripted inserts, so this definitely makes my life a little simpler!

peace

Unattended Visual Source Safe 2005 installation

To install Visual Source Safe 2005 silently, you must first create an install ini file using Setup.exe.
The setup in the root of the CD will not do this, you have to navigate to the setup folder on the CD.
If you are using a download from MSDN, there is only one setup.exe and it’s the one to use.
The machine that you use to create this ini will be the template for all installations. You cannot create the ini file from a machine that already has VSS installed. You must create it from a machine that does not yet have VSS installed.

From a command line, execute the following:

\\(servername)\(sharename)\(VSS Path)\setup.exe /CreateUnattend \\(servername)\(sharename)\(Path to INI)\filename.ini

I create my ini file in the same folder as the setup process.
For example, my command would look like this:

\\VSSServer\VSS\setup.exe /CreateUnattend \\VSSServer\VSS\FullInstall.ini

Again, the machine that you are running this process on should not have VSS installed yet. If it does have VSS installed, the ini file will be used to upgrade or add/remove features and will not install VSS on the target machine.

To install on the target machine, run this command:

\\(servername)\(sharename)\(VSS Path)\setup.exe /UnattendFile \\(servername)\(sharename)\(Path to INI)\filename.ini

Repeat Table Header on Each Page in SSRS 2008R2

I ran into an issue with the tablix in SSRS 2008R2 where I set the tablix property “RepeatColumnHeaders” to True. However, this did not cause the table header to repeat.

After a little experimentation, and a little help from Paul Turley, I figured out how to consistently get the table header to repeat on every page.

The setting of the “RepeatColumnHeaders” property on the tablix seems to have no effect on this behavior. Instead, we have to set it on a static member of the row groups.

To see the static members of the row groups, click on the small drop down arrow on the far right of the row groups/columns groups header. Then select Advanced Mode.

With the static members of the row group shown, select the first one in the list. The top left cell of the tablix should be selected in the designer.

Now open the property pane and find the property “RepeatOnNewPage” and set it to True.

Then find the property “KeepWithGroup” and make sure its set to After. If its not, set it to After.

Now your table header will repeat on every page.

BTW, check out Paul’s book “Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports” It has been a good resource for SSRS.
peace

SSIS 2008 / SQL Server 2008 Connectivity Issue

We recently upgraded to SSIS 2008 and SQL Server 2008 r2. This helped the performance of a large package that contained 27 lookups for a fact table. It used an OLEDB destination component, set to Table – Fast Load. It would process a million rows in less than 2 minutes.

This worked well, until I added a multicast and an OLE Command component to the data flow. What I wanted was to check the values passed to the stored procedure and add the existing row to history if there was a change. The OLE Command used the same connection manager as the main data insert, since the history and the current tables were in the same database.

When running it, I would get to about 100,000 rows, and then SSIS would begin to slow down and finally it would lose network connectivity to the SQL Server. (We have a separate SSIS server) This is the error that was written to the logs:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Named Pipes Provider: No process is on the other end of the pipe."

I began by troubleshooting the network, looking for errors on the switch and both servers. This yielded nothing, there were no errors logged. So I turned to SSIS, figuring that it somehow was the culprit since everything worked until I added the multicast.

I removed the multicast and the OLE Command components and tested again. The package processed a million rows in less than 2 minutes.

I added the multicast back in, without the OLE Command and tested. Again it processed a million rows in less than 2 minutes.

I added the OLE Command component back in and tested. It failed at 49,000 records.

I added another connection manager, set the same properties and then pointed the OLE Command to the new manager. When I tested, I was surprised to see it complete successfully!

It seems that the one connection manager was not able to handle the multiple destinations simultaneously. I have no way to confirm this at this time, but since I have multiple connection managers for the same database, I have not experienced this issue at all.

SSRS Fix for FireFox/Chrome Report Size

If you have ever used Chrome or FireFox with SSRS, you know the report window can show up very small. This is due to the way that SSRS implements the IFRAME.

A quick fix for this is to add the following entry to the ReportingServices.css file.

.DocMapAndReportFrame
{
min-height: 800px;
min-width: 800px;
}

You can usually find the ReportingServices.css file at: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles, however your location may be different depending on your installation.

peace

Using Maximum Insert Commit Size with FAST LOAD on the OLE DB Connector, and a Warning

I have been working on some new ETL recently, which of course has me optimizing the ETL. One of the things that I look at while optimizing is the Maximum Insert Commit Size (MICS) one the OLEDB connector when using the Table – FAST LOAD option.

This setting controls how many records are sent and committed to a table at a time. For example, if you have a 12,000 record source, and you set MICS to 5,000, you will have 3 bulk insert statements sent to the server: two with 5,000 records and 1 with 2,000 records.

The beauty of doing this is that it helps keep the transaction log small if you use simple logging. (On staging databases, I routinely use simple logging since there is no need to have full logging). It can also increase performance since the server is dealing with smaller chunks of data at one time.

But, like all good things, there is something you should watch out for. If you set up redirect row on error in the OLEDB Destination and one record in a batch fails, that one record will be redirected to the error output, but the entire batch will not be inserted into the database! The only indication you will get is that one record failure.

I have tried to set the MICS to 1 for safety, but that ends up being slower than the normal Table Load.
What I wound up doing is using the normal table load on smaller tables, and removing the redirect row error handling on larger tables. I then put in a derived column step that clean the data before inserting it. This allows me to load a 46 million row table in a few minutes, with a transaction log that doesn’t get above 10 Megs.

So, definitely work with the MICS setting to further optimize your ETL, but if you are using error handling to redirect rows, be aware that you will lose data each time a row is redirected.

Setting a NULL value in SSIS derived column

Ran into an interesting error while trying to set a null value to a column in a derived column task in SSIS.
Here is my statement:

[CustNum]=="" ? NULL(DT_STR, 4, 1252) : [CustNum]

And the error thrown:

For operands of the conditional operator, the data type DT_STR is supported only for input and cast operations.

The correct syntax for this is:

[CustNum]=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : [CustNum]

Not sure why this requires the explicit cast since you include the type in the null statement.

peace

 

 

My Z80 Homebrew Computer - The Pony80