Grumpy Old DBA

SQL Server 2008 Information

I was searching for a particular document for SQL 2008 and thought I'd list the links to what I found, there's some especially good white papers in Technet, I can recommend the T-SQL enhancements and the indexed view white papers especially:

SQL Server 2008 White Papers         http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

Technet SQL 2008 White Papers      http://technet.microsoft.com/en-us/library/bb418496.aspx

A list of blogs and other sites, sadly no Grumpy Old DBA   http://msdn.microsoft.com/en-us/sqlserver/bb671054.aspx

Testing Times - mdf fragmentation

Now I've never been convinced that file level fragmentation is irrelevant on a SAN, at least two sources have assured me that this is the case, but then they also assured me of a number of other points most of which it appears I have proved to be basically incorrect. < grin >

Now I've followed with great interest a series of posts by Linchi Shea  http://sqlblog.com/blogs/linchi_shea/ concerning SAN fragmentation, if you don't subscribe to this blog then you're really missing a great source of technical knowledge, without this blog I would have struggled ( well I still struggled but that's another story ) with trying to get the storage teams to consider HBA queue depth for a start.

So I decided to come up with the insane fragmentation test: Assume that there is no DBA and you're creating a number of databases on your server; here's what I did;

I created three databases and then ran my test 1,  which creates and populates a 1 million row table of approx 8.5GB, in each database simultaneously. The population of the databases caused auto growth and true to form windows + sql server managed to totally fragment all three databases despite ample free space on the LUN.

On a serious note this should be taken as a warning as to what can happen if you don't manage database growth. Here's the output showing my fragmented databases

 The three databases are called Stresful, rubbish1 and rubbish2.

• I then emptied the datafiles of data and then ran my scripts which populated the three databases with 1 million rows of data each, I did this three times.
• I dropped all the databases, created non fragmented databases and repeated the test three times.
• The three runs against the fragmented databases took on average 106 mins.
• The three runs on non fragmented databases took on average 16 mins

It's not for me to name the particular SAN that I used for this test but it is a serious enterprise bit of kit and is claimed to be able to scale with every condition known to man and maybe a few more!  To the best of my knowledge the HBAs are 4GB, there's more than 1 of course because this test was run on a cluster.

• Logically as this was all data inserts I knew the performance would be bad and I intend to repeat the test with a smaller number of fragments and to test updates as well as inserts, see my other posts.
• We don't always have just one database on our servers and we don't always have a sperate lun for each mdf file, much as some of my tests showed that creating multiple files ( not filegroups ) for your database could degrade performance the movements of the disk heads I would suggest are the factor here.

Linchi only did a test on single files, maybe running multiple tests would produce a different result.

Maybe the particular SAN I'm testing against is different, sadly like probably most DBAs I don't have acess to multiple hardware platforms, it's only due to a data centre migration I've been able to run these tests, I'm attempting to show that the migration does not bring any degredation of performance compared to our current data centre.

I should give a quick few words of thanks to Tony Rogerson http://sqlblogcasts.com/blogs/tonyrogerson/  who has fended a number of my questions and who also run SAN benchmarks  http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/09/22/1089.aspx 

I hope this post formats correctly - always a bit of a gamble when adding images!

CompareStorage@GrumpyOldDBA

As a slightly seasonal touch of lightness I though I’d just do a comparison of DAS and SAN storage

At a recent user group meeting I posed the question about DAS vs SAN performance and were DBAs generally very satisfied with SAN storage. The overall impression I got was that generally us DBAs don’t feel we get value from the tin and that the storage guys are not very keen to talk to us.

A disk is a disk is a disk so I’m often slightly perplexed that when I test I get such differing results, it’s pretty obvious to me that there is contention on most sans, and here I mean the whole Storage Area Network not just the actual storage.

I’m still waiting for a rational explanation as to why a handful of internal SAS disks can consistently outperform a couple of very expensive SANs over a wide range of different tests including application code.

As ultimately the systems I tend to support all have a requirement for performance I find it increasingly difficult to justify the use of a SAN with SQL Server.

As a byline I’ve just replaced the hard drive in one of my laptops with a SSD , http://www.offtek.co.uk/item_detail.php?id=575331&maincat=1&subcat=99

It’s not the fastest SSD in the world but it does appear to make the whole laptop so much quicker, certainly startup of XP is pretty quick, as laptop disks are so slow this is quite a good way to breathe new life into your laptop.

I’m still thinking about SSD for my server !!

Testing Times 4 - 4 semiT gnitseT

Yeah I know it's a silly title but I decided to up my testing by mirroring the database that I populate, that way I can create havoc on two servers at the same time and get a feel for how mirroring might be of use to me in the future, or not.

One slight struggle was that I didn't really have enough SQL 2005 enterprise class servers to use, in this instance I couldn't see any value in using a collection of PC's and/or laptops, so all the Servers are pretty hefty x64 boxes.

What did I learn initially? Well using Standard Edition SQL Server is a total no-no as the mirroring degrades performance so badly that it becomes unusable.
test 2 which might run out at around 14 mins was still running some 2 hours later at which point I killed it.
In "fast" mode the mirroring appears to roughly halve performance, I only have 200mbs network connectivity but network activity showed at barely 1% so I figure network wasn't the issue.

Updates seem to fare better than inserts with test 3 seeming to hold up better in proprtion to test 2.

One possible use for me was to make use of the snapshot facility on the mirror, well here's how that worked out! I emptied my databases but left mirroring in place, databases presized to 80GB, I then ran my insert test ( Test 2 ) which populates 4 x 1 million row tables. The actual insert completed quite quickly, about 40% longer than without mirroring. However it took nearly 3 hours for the mirror to synchronise and 2 hours 20 mins for my snapshot to be created.
What I intended was to grab a snapshot before all the data was loaded, what I actually got was a snapshot after all the data had loaded.
OK it's an extreme test I suppose and I used mirroring out of the box, no performance tweaks. From a network point of view the traffic is so low that one can say mirroring doesn't add any appreciable overhead.
So to sum up mirroring didn't give me the parallel load for testing I was hoping to simulate

Testing Times 3

( I was on holiday for three weeks so sorry for the delays )

In my previous posts I briefly mentioned the task in establishing a method to benchmark a SQL Server new build or migration.
This hasn't been an easy task by any means and I am under pressure not to make any posts based around non disclosure agreements and confidentiality statements in emails. Now I would never name names anyway unless I had agreement and I'm not on a campaign to discredit any storage provider or data centre provider, but I am interested in the technical performance differences between systems and storage and how as a DBA I can measure this and hopefully share information such that as a group DBAs can get the best performance for their clients or employers and understand where performance issues may arise and how to identify them.
I have posts to come on this area of testing which will consider the use of IOmeter and if it has any relevance to SQL Server and what we can get out of Perfmon. I will also talk about storage in a very general manner, because this is the "sensitive" area.

Just to give some background on my experience here; I have two certifications in fibre channel architecture, no it doesn't make me anything but a casual observer but the courses did give me an insight into this aspect of storage using networking. I've worked with storage arrays since sql server 6.5, I had a sql server 6.5 running on multiple storage arrays using raid 10 around 10 years ago and I've worked with external storage and SANs ever since. In fact my first experience with a RDBMS was Ingres running on Solaris, with a WFWG 3.1 front end, and we were using striped drives then - I always imagined everybody ran like this as this was my first introduction. Anyway I digress:- Performance has always been important to me and it's in this area that I usually work, generally hardware is a minor part of tuning, the old addage used to be that it was 90% code and 10% hardware; however, it seems that this may not be the case quite so much any more.

So what else have I been looking at? Multiple files for the database, multiple filegroups for the database, multiple drives. The fibre channel architecture , the storage guys -- and it's all a bit interesting.

So here's my basic set of tests, a sample set of results and the scripts so you can do this yourself.

http://www.grumpyolddba.co.uk/Infrastructure/sqlbenchmark.htm

So what do I need to say about this? I've run all the tests on an enterprise server, nominally let's say it is a 4 x quad core with 32gb of ram. All the scripts were run from a TS session on the actual server to avoid any issues with networking. All the scripts were run manually from a Query window.
OK you can't get the index stats out of SQL 2000, but the tests are designed to run on SQL 2000/2005/2008. You compare the run times to get comparision.
I created a database in simple recovery with a single 80GB mdf and a 10GB ldf. SQL settings are all out of the box.
I cleared the proc and buffer cache before each test run, but no stats updates. For the index stats I took the database on and off line to clear the dmvs so I could just show what each test did in terms of io.

You may ask why I should want to publish this and why ndas have been mentioned. Well having used these tests to compare performance of essentially SQL Server working with a different storage setup the tests were dismissed as not being relevant to a production OLTP database and not a valid way to compare performance, see Testing Times 1 to get a feeling for where this is leading.

Anyway, more to come and please please I would be grateful for any feedback on whether you think I have a reasonable set of tests. Feel free to email me direct - there's an email address on www.grumpyolddba.co.uk - if you don't want to leave a comment.

Testing Times 2

Just sometimes it's possible to get to a position of not being able to see the wood for the trees.
I'm indebted to Paul Randal http://sqlskills.com/blogs/paul who returned me to normality after one of those lapses of reasoning.
You'll see in my previous post that I'm currently establishing some performance baselines ahead of a move to a server upgrade.
As part of my upgrade strategy to SQL2005 I was considering making the database sit on multifile database ( not filegroups ) so I figured that this could be an opportune time to test this out with my sql tests.
Sadly the first tests on a multifile database showed quite a severe performance degredation and I was at a loss to quite understand why this should be so on raid sets.
Paul pointed out the obvious, each file will cause head movement on the disks as the round robin of the multiple files every 64kb will have the data hopping all over the place. So after a quick "Doh" I thought about my setup, the 4 disk raid 10 would obviously not work so well as there are only two disks, but my san with lots of spindles should work fine - or not.
I have to complete the full set of tests but indications are that for my tests at least a multifile database is a very bad idea.
I have a 16 core server so had used 8 files in the primary filegroup; performance appeared to degrade roughly 50%, which is significant.
Full details by the end of the week!

Testing Times 1

It's an interesting time with being asked to performance test/benchmark a new server setup for a migration of sql server and data centre.
Both current and new servers are SAN attached and it has been what is effectively the testing of the storage which has proved interesting.
 
To add to the mix I have a similar spec server with internal disk and I ran a couple of tests on one of my my home servers ( essentially a pc with lots of disks )
The three proper servers are modern top spec kit with similar processors - the standalone having the slowest clock speed
 
The hardware stacks up like this:
Server 1 - current:
x32 sql 2000 4 x quad xeon 2.9Mhz 32gb ram, san attached, raid 10 data ( 10 x 15k ), raid 1 logs ( 15k )
Server 2 - new:
x64 sql 2005 4 x quad xeon 2.9Mhz 64gb ram, virtualised san
Server 3 - standalone:
x64 sql2005 4 x quad xeon 2.4Mhz 32gb ram, raid 10 data ( 4 x 10k sas ), raid 1 logs ( 10k sas )
Server 4 - home:
x64 sql 2008 1 x dual core amd 4200 8gb ram, hardware raid 0 data ( 4 x 7.2k sata ), single 7.2k sata logs.
 
So on a basic test of inserting 1 million rows into a table ( has 4 secondary indexes and a text column ) generating 8.5 Gb of data where would you rank the  
servers?     Vote now on 087xxxxxx etc. etc.
 
Average results of several runs:- ( single table test )
 
Server 3     6 mins 30 secs            2,564 rows/sec   22Mb/sec  1,330Mb/min
Server 4     9 mins                    1,851 rows/sec   16Mb/sec     961Mb/min
Server 1     11 mins                   1,515 rows/sec   13Mb/sec     786Mb/min
Server 2     17 mins                     980 rows/sec   8.5Mb/sec    509Mb/min
 
( I'm currently putting a doc together on all the tests including the code )

Indexes, Basically - from SQL Bits

I'll put the entire content including as much as I can remember of what I said, the demo code and results onto my web site over the following week or so.
If you'd like the demo databases ( sql 2008 ) there are three, the largest being 60Gb - none really work on a laptop unless you like long waits between queries,
dop me a mail with your address and I'll send a set of native backups with the demo scripts.

( nothing tonight as I'm off to see Metallica at the O2 )

The following cover a few points I raised.

covering clustered indexes

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/05/26/covering-clustered-indexes.aspx

size does matter

http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%204.mht

( The excel spreedsheet which goes with size does matter)

http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%204.mht

When is a number not a number?

 Bob Dorr has posted about conversion issues with isnumeric

http://blogs.msdn.com/psssql/archive/2008/08/25/isnumeric-returns-1-true-and-i-expected-0-false.aspx?CommentPosted=true#commentmessage

This reminds me of a difficult "bug" in an application whereby a lookup actually entered a numeric value into a table from a text list. For the user the list was too long - why some developers think a drop down list of over a thousand entries is a "good idea" I never know - anyway the users had discovered that you could bypass the drop down to get an unknown by entering a full stop ( decimal point )  Now if you type

Select isnumeric('.');  this will return true/1 ,  however  select convert(int,'.');  will fail.

Sadly for the application in question the data entry didn't fail it was a process which occurred somewhat later - it took some considerable time to sort it out. Personally I wouldn't allow users to enter commas and full stops but it's worth noting that the logic behind a function doesn't always follow what we might consider "commonsense decisions" as amany a bug will prove.

" Seeing " data.

Sometimes the visualisation of data can be difficult, consider displaying user activity for a multinational application.
This is exactly what I wanted to do in such a manner that I could see who and when was using the system, however with over fifty countries at a five minute granularity this means a grid of 288 x 50 cells and even if you could get it on screen the actual information would be difficult to take in.
My solution was to use SSRS and an area graph, the link below shows a sanitised version of the report but it gives a flavour of the real thing, the only thing not changed are the times and colours.

http://www.grumpyolddba.co.uk/monitoring/MERKATActivity.htm

The actual report has real country names, links to tabular reports and each country has a click through to show a line graph for that country's activity for the day. The report also takes a parameter to allow historical reporting and can show activity for the current day in near real time , sadly it can't forward predict as a colleague suggested it might!

It's actually a very easy way to see if you have available windows of low activity for maintenance and it's easy for anyone who might be interested in system use. It can also answer questions such as how many users from Xyz were in today and overall system peaks are easy to view.

There's no code as the data comes from an application. This is part of a suite of reports for tracking server and application performance, I'll publish more at another time.

SQL Versions Performance Benchmarks

http://sqlblog.com/blogs/joe_chang/archive/2008/08/17/large-query-performance-from-sql-server-2000-to-2008-32-64-bit.aspx

Joe has published a well readable article.

Cannot generate SSPI context

Now here's a slightly interesting point about this error. Although the kb does actually mention this reason for the error it is about the last thing on the article, a footnote, and it doesn't specifically word it to cover this scenario, and we all read the entire KB before doing anything - right ?

So the scenario was that a SQL 2005 x64 SP2 + CU7 was given a service account, previously it had been running local system ( default install ).
After the SQL Service was restarted the users could not connect from their workstations with integrated security, sql connections worked fine, receiving this error ( Cannot generate SSPI context ), using rdp  and integrated security everything was fine so it was a sort of inconsistent error.

Now one of what I define a real pain in the A*** with SQL Server is full text search - even if you don't install it from start every service pack will put it back, in all the years I've worked with SQL Server I've never had reason to use full text search, but every server I encounter has been blessed with service running - argghhh!

Well as we don't use Full text on this particular server, it's a DW box, the service account had only been changed for the Engine and Agent, sadly full text was running and as it was running as local system this is what stopped the logins.
The solution is to either disable Full Text ( until the next patch/sp ! ) or set the service to use the service account and then disable it.

http://support.microsoft.com/default.aspx/kb/811889

Filtered Indexes - Really Cool !!!!!!!!!!!!!!!

You may or may not be aware that one of the major new features for SQL 2008 is filtered indexes. Now I'm not quite sure why more hasn't been made of this as it is without doubt a seriously significant new feature.
I suppose it depends upon your application(s) on how significant this feature is and as with any new feature there exists the possibility of seriously causing problems if you get it wrong - or more significantly don't document your database. Everyone documents their databases surely ?????
Let us condsider simple issue for us in the UK - you have a table which stores personnel data including National Insurance numbers, each national insurance number must be unique but not all new employees know their NI number so the column must allow nulls, and multiple nulls at that.

Now basic DBA stuff here, if I ever interview you this is a question I'll probably ask. How do we index the National Insurance number column?
A unique index/constraint will only allow one null which is not acceptable and no constraint is also unacceptable.
Well up to now to resolve this problem you create an indexed view on the NI column --
e.g.

create view vw_NIcheck

select NIColumn from dbo.personnel where NIColumn is not null

now create a unique clustered index on the view. This allows you to have multiple nulls but unique NI numbers.

With SQL Server 2008 you can now create the following index:-

the index only covers the non null values and thus you can have multiple nulls and unique NI numbers. Pretty cool eh?

Well there's more, to coin a phrase, and I'll post example code and query plans later to show this working in a practial manner.

If you're working with large tables and your application is multinational then index rebuilds can be a bit of a nightmare. I've worked where individual secondary indexes range between 10 and 20Gb, this was a moderate sized database and I'm well aware there will be bigger and bigger databases out there,
but this is based upon my personal experience.
Should your database be used 24 hours a day when do you rebuild? whatever you do you'll block one group of users and if your table/index is being constantly used the on-line option is unlikely to be of much practical use.
So filtered indexes to the rescue : tada tadt tada da! ( a fanfare )
Taking our 20Gb index we now create a number of smaller filtered indexes, we filter by country or region say,

Immediate gains are the fact that the individual indexes will be much smaller, a divide and conqueur approach, and if we rebuild the index for China it won't block users in the USA.
The filtered column(s) don't even have to be in the actual index.

Coupled with or without partitioned tables this will be a big step forward for 7 x 24 applications and for databases with large tables ( and indexes ) where the window for such maintenance is short, It's likely to take less time ( and resource ) to rebuild ten 1GB indexes than one 10GB index.
I'll hopefully post working examples next week.

Performance Dashboard for SQL 2008

Just in case you haven't seen this   http://sqlblogcasts.com/blogs/thepremiers/archive/2008/06/20/sql-server-2008-performance-studio.aspx

then have a read.  As I actually create dashboards and performance reporting for sql 2000 and sql 2008, and have been for many years, I'm seeing some of my work made redundant but there's other new features in 2008 I can concentrate on so it's not all bad < grin >

Tracking problem indexes in SQL 2000

• It’s all so easy(ish) to work within SQL 2005 but the reality is that there are still more SQL 2000 databases than SQL 2005, so I’m told, and I’m supporting one of them right now.
• I’ve been contemplating on how to get a handle on which of my indexes are fragmenting too quickly and where the high levels of page splits are occurring.
• So I know you’ll all say well you can run dbcc showcontig  and  of course this is true, but not on a very busy production server when it’s running hot – and it’s at times like these that I want this information not after the busy times or when the server is off-line.
• Well the simple way to handle this is to create a log shipped database and run your commands on the log shipped server, I run a dbcc showcontig several times a day and put the data into a table, the job retries several times on failure as it has to run within the log restore intervals. It’s my own log shipping so I have a certain amount of flexibility in this.
• So capturing dbcc showcontig allows me to build up a picture of my fragmenting indexes, it can be interesting to note that some indexes have 80% fragmentation within a few hours of being totally rebuilt.

Here’s the query:-