Thursday, August 11, 2011

Go!

Can you guess it's a slow time for me at work? This is my third post within one week! During downtime I like to read articles to further my knowledge of SQL Server. That's when I catch up on newsletters that collected in my inbox or prepare for my upcoming certification test.

Today, I ran across Kalen Delaney's article about a curious undocumented feature of SQL Server. If you put an integer after the batch separator "go" (e.g. go 10) the sql statement(s) before the "go" will be executed that many times (in my example 10 times.)

She uses this interesting little feature to populate test tables with test data but it can be used as a quick and dirty stress/concurrency testing technique as well.

Thank you Kalen for documenting the undocumented!

Wednesday, August 10, 2011

SQL Server Data Collector Woes

This post is to list the idiosyncrasies I came across while setting up Data Collector.

Cache directory
Issue: As the name implies this is the location where the cache collects the data. When clicking on the ellipsis it opens up the drive and folder structure of the server where the management data warehouse resides. Guess what? If you specify a folder on that server you will get the following error: The system cannot find the file specified.
Solution: The folder needs to exist on the server against which the data collection is executed.
Tip: This folder seems to be used even if you set the data collection to be non-cached.

Permissions
Issue: Login failed for user error.
Solution: Make sure the user set up to run the SQL Agent Service has access to the database server where the management data warehouse resides.

QueryActivityUpload
Issue: The error "Arithmetic overflow error converting expression to data type int" during Query Statistics processing.
Solution: As per this Connect page explains Cumulative Update #5 for SQL Server 2008 SP1 will fix this problem. However, if you cannot wait to get and apply that update there is a manual workaround explained on that same page. The workaround involves modifying the QueryActivityUpload SSIS package. I went by that route and the modifying the package does work. I just want to add that There are 2 places where the fix needs to be applied: OLE DB Source in the DFT - Create Interesting Queries Last Snapshot and the ODS - Get current snapshot of dm_exec_query_stats in the DFT - Create Interesting Queries Upload Bath dataflow tasks.

SQL Server Deprecated Features

I ran across this very useful performance counter today: SQLServer:Deprecated Features. This shows you how often SQL Server encountered listed deprecated features since the latest start.

In my SQL 2008 environment I see such item as "String literals as column aliases" or "sp_change_users_login." Some of these features are self-explanatory. For example, I can deduct from "Table hint without WITH" what I need to use the keyword "WITH" for table hints to correct this for a future version of SQL Server when skipping WITH will not be allowed.

For some others it's not so obvious or I need to find out another option. In those cases I can look at Microsoft's page that describes all the deprecated features in detail giving alternatives: http://technet.microsoft.com/en-us/library/bb510662.aspx. On this article, for instance, I find out that instead of DBCC DBREINDEX I need to use the REBUILD option of ALTER INDEX.

If you have a lot of legacy code in your application (and who doesn't?) be sure to take a look at this counter by running the following SQL statement: SELECT * FROM sys.dm_os_performance_counters where object_name = 'SQLServer:Deprecated Features'