SQL 2008 : Multi Server Queries

Management Studio in SQL 2008 introduces Multi Server Queries :)
When enabled, you can run the same SQL against multiple servers simultaneously and receive the results in a single window.
It's a feature third party tools have had for a while.
Cool, but somewhat dangerous too...

http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

CTEs - Common Table Expressions

'a CTE can only be used once in your query' (by the query the immediately follows it).

source :
http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

Vista Volume (or lack of...)

Where's my volume icon gone?
Why is my powersave icon suddenly visible even when the laptop is plugged in?

This chap has some solutions >

http://winhlp.com/node/16

It's a long , detailed article which lists several solutions. If you find your system icons missing, here's the quick fix >

1. Search registry and delete all “IconStreams” and “PastIconStreams” entries (searching for the former, will find the latter.
2. Restart Windoze
3. Add them again (task bar properties, notification area > tick the boxes!)

SQL 2008 CU1 Released

http://support.microsoft.com/kb/956717/

CU1 (or cummulative update 1) is out already for SQL 2008.
CU releases havent replaced service packs, but are a lot more regular if SQL 2005 updates were anything to go by.

Common Table Expressions : MAXRECURSION

I was looking today to generate a sequential list of dates as quickly as possible.

The fastest solution to the problem seemed to involve a common table expression (CTE) in sql 2005.

CREATE FUNCTION GenerateDates(@startdate DATETIME, @enddate DATETIME)
RETURNS TABLE
AS
RETURN 
(
      WITH DatesCTE(CurrentDate) AS
      (
            SELECT @startdate as CurrentDate
            UNION ALL
            SELECT DATEADD(day,1,CurrentDate)
            FROM DatesCTE
            WHERE CurrentDate < @enddate 
   
      ) 
      SELECT CurrentDate FROM DatesCTE
);

Excellent execution time, but there is a downfall. The function cannot cope with providing a list of more than 100 dates.

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is because the defalt recursion level for a CTE is 100.

Normally the recursion level can be specified manually to get round this using OPTION (MAXRECURSION limit) where limit is a value from 10 to 32767, or 0 for unlimited recursion.

Setting the recursion level with OPTION (MAXRECURSION limit) is NOT SUPPORTED inside user defined functions and views.

Not as tidy as using a function, but we can do with a stored procedure >

CREATE PROCEDURE dbo.usp_GenerateDates
  @startdate DATETIME ,@enddate DATETIME
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
  (
    SELECT @startdate AS CurrentDate
    UNION ALL
    SELECT DATEADD(day,1,CurrentDate)
    FROM DatesCTE
    WHERE CurrentDate < @enddate  
  ) 

SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO

We can put the output of the procedure into a temporary table like this >

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
   DROP TABLE #tblDateList
 
CREATE TABLE #tblDateList
   ( [Date] DATETIME NOT NULL,
     CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC) 
   ) ON [PRIMARY]
  
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SET @enddate   = CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
SET @startdate = DATEADD(DAY,-100,@enddate)

INSERT INTO #tblDateList
EXECUTE dbo.usp_GenerateDates
  @startdate = @startdate
 ,@enddate = @enddate

Disk System Pressure

Woah!!!!! This sql instance is really struggling with I/O >


The steps I took to rectify this were >
1) File Defragmentation (well I moved the files off and back on to the disk as it was quicker)
2) Data Defragmentation (implemented a deframentation job & scheduled it)
3) Moved tempdb to a separate physical disk

Performance Impact of Enabling Page Checksum and Default Trace

Whilst hunting options to squeeze every last drop out of a life out of an SQL instance, i toyed with the idea of disabling the database 'Page Checksum' and 'Default Trace' options.

The full article is linked below, but the upshot is that there is almost negligable performance decrease.

http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

OLTP Performance Issues

Rather useful high level summary >

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx

Forced Parameterization

SQL Server's query optimizer can make use of cached plans if a query is submitted that already has one.
The random nature of adhoc SQL against a database makes it difficult to reuse a plan however as similar adhoc tsql queries just dont look the same.
Turning Parameterization to 'forced' tries to improve this and alledgedly makes a difference for high volume systems.
Am currently trialing it out...

-- change parameterization to 'forced'
ALTER DATABASE databasename SET PARAMETERIZATION FORCED
GO

-- reverse the setting
ALTER DATABASE databasename SET PARAMETERIZATION Simple
GO

Table Row Count GEM !!!

Use System objects to return the count of records in tables, rather than COUNT(*).

This method is runs almost instantly but may be a little behind in terms of table statistics, i.e. the row counts may be out as statistics are not updated instantly.

SELECT 
      SCHEMA_NAME(o.SCHEMA_ID) SchemaName
    , OBJECT_NAME(o.OBJECT_ID) TableName
    , SUM(ROWS) TableRows 
FROM SYS.PARTITIONS p
INNER JOIN SYS.OBJECTS o 
   ON p.object_id = o.object_id  
WHERE INDEX_ID IN (0,1) 
GROUP BY o.SCHEMA_ID, o.OBJECT_ID 
ORDER BY 1,2,3

Statistics can be updated using DBCC UPDATEUSAGE

Link :
http://furrukhbaig.wordpress.com/2008/07/24/rowcount-for-large-tables/

Nov 2009 Update. Plenty of alternate versions on the web now, linking different system tables >
http://www.bimonkey.com/tag/row-count/
http://www.sqldev.org/transactsql/perform-a-row-count-without-causing-a-scan-60203.shtml

SQL Statman ??? (Ski-Ba-Bop-Ba-Dop-Bop)

Statman sounds a bit too much like Scatman for my liking and reminds me of a cheesey 90s dance song.

On a serious note, just what are all these Statman() entries in my sql trace?

for example >

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [dbname] AS [SC0] FROM [dbo].[#tmp_sp_db_vardecimal_storage_format ....

The short answer is that they are nothing to worry about.
They show an automatic statistics update occuring i.e. are the effect of having AUTO_UPDATE_STATISTICS set to ON.

TempDb Optimization for Multiple Processors

"The current recommendation from Microsoft is that you have a file per logical CPU for tempdb so as to balance load not only for object creation but for extent allocation and performance."

Ref : SQL Server write behaviour with multiple files in a file group

So, split TempDB by the number of processors.
Using current size of TempDB as a guide (2GB) and 2 Processors, split to 2x 1GB files.

My previous blog entry on how to do > Moving / Adding files to TempDB

SQL Messages Window : Get Messages straight away!

If you use PRINT to pass status messages back when running a TSQL script, you typically dont get to see them until the script completes (or errors) and then, they appear all at once (not great for debugging...)

DECLARE @StatusMessage varchar (100)
SET @StatusMessage = 'Insert Finished, Update Starting : ' + CONVERT (CHAR (20), GETDATE (), 8)
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 

http://sqlblogcasts.com/blogs/leopasta/archive/2007/07/03/instant-gratification.aspx

Hyper-V : SQL Support

SQL Server 2005 is not fully supported on a virtual machine in a Windows Server 2008 Hyper-V environment. Microsoft is considering whether to provide support for SQL Server 2005 on Hyper-V virtual machines in future updates of SQL Server 2005 >

http://support.microsoft.com/kb/956893

( SQL Server 2008 is supported )

Decision time it is then, as currently building Hyper-V images...

Remote Desktops Tool for Vista

I'm a big fan of 'Remote Desktops', the MMC snap-in that combines a pane listing servers with RDP functionality.
For XP, this was provided by 'AdminPak'.

For Vista this is now known as RSAT. (Remote Server Administration Tools).

AdminPak for XP >
http://www.microsoft.com/downloads/details.aspx?familyid=c16ae515-c8f4-47ef-a1e4-a8dcbacff8e3&displaylang=en

RSAT for Vista >
http://www.microsoft.com/downloads/details.aspx?FamilyId=9FF6E897-23CE-4A36-B7FC-D52065DE9960&displaylang=en

New Browser : Google Chrome

Google Chrome is launched in Beta today.

Having played with it briefly, it is FAST and does initialy look worth the effort.

http://www.google.com/chrome/


oh, and this ZDNet report agrees with me about it's speed.

Random Password Generator

For when you cannot install Chaos Generator (local pc locked down!), there are multiple online password generators now >

http://www.pctools.com/guides/password/

This Leet translator is also good for passwords >

http://www.albinoblacksheep.com/text/leet

r