Grumpy Old DBA

19 July 2008 12:08

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

Posted by GrumpyOldDBA with no comments

Filed under: File Under Grumpy, Diagnostics, operating systems, x64

11 July 2008 18:46

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

as

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:-

create unique index idx_personnel_NINumber on dbo.personnel(NIColumn) where NIColumn is not null;

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,

create index idx_mytable_USA on dbo.mytable(c1,c2,c3,c4,c5) where country='USA';

create index idx_mytable_UK on dbo.mytable(c1,c2,c3,c4,c5) where country='UK';

create index idx_mytable_China on dbo.mytable(c1,c2,c3,c4,c5) where country='China';

create index idx_mytable_Antigua on dbo.mytable(c1,c2,c3,c4,c5) where country='Antigua';

create index idx_mytable_Kuwait on dbo.mytable(c1,c2,c3,c4,c5) where country='Kuwait';

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.

Posted by GrumpyOldDBA with 3 comment(s)

Filed under: Indexes, Partitioning, SQL2008

23 June 2008 09:19

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 >

Posted by GrumpyOldDBA with no comments

Filed under: Diagnostics, Trending and Statistics, SQL2008

10 June 2008 20:30

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:-

insert into DBADatabase.dbo.Mydbfraglist(ObjectName, ObjectId , IndexName, IndexId , Lvl , CountPages , CountRows , MinRecSize , MaxRecSize , AvgRecSize , ForRecCount , Extents , ExtentSwitches , AvgFreeBytes , AvgPageDensity , ScanDensity , BestCount , ActualCount , LogicalFrag , ExtentFrag)

exec ('dbcc showcontig with all_indexes,tableresults,NO_INFOMSGS')

Here’s the table:-

CREATE TABLE [dbo].[ Mydbfraglist] ( [ObjectName] [char] (255) NULL , [ObjectId] [int] NULL , [IndexName] [char] (255) NULL , [IndexId] [int] NULL , [Lvl] [int] NULL , [CountPages] [int] NULL , [CountRows] [int] NULL , [MinRecSize] [int] NULL , [MaxRecSize] [int] NULL , [AvgRecSize] [int] NULL , [ForRecCount] [int] NULL , [Extents] [int] NULL , [ExtentSwitches] [int] NULL , [AvgFreeBytes] [int] NULL , [AvgPageDensity] [int] NULL , [ScanDensity] [decimal](18, 0) NULL , [BestCount] [int] NULL , [ActualCount] [int] NULL , [LogicalFrag] [decimal](18, 0) NULL , [ExtentFrag] [decimal](18, 0) NULL , [TheDate] [datetime] NULL ) ON [PRIMARY]GO ALTER TABLE [dbo].[ Mydbfraglist] ADD CONSTRAINT [DF_ Mydbfraglist _TheDate] DEFAULT (getdate()) FOR [TheDate]GO Run this query to extract the datetimes of the showcontig select distinct thedate from DBADatabase.dbo. Mydbfraglist order by thedate desc; --cut and paste the required datetime into this query and adjust for scan density ( here set to 50% ) select countrows,rtrim(objectname)+'.'+rtrim(indexname) as tableindex, indexid,scandensity from DBADatabase.dbo. Mydbfraglistwhere scandensity<50 and objectid>14 and countpages>16 and thedate='2008-05-07 18:48:32.587' order by thedate desc; ============================================================================================ so this will allow you to see which indexes are fragmenting badly but how about page splits?

Well you can do a similar trick with dbcc updateusage , but it’s not so easy to put into a table and you can’t run it against a read only database. You can break your log shipping or run it against production.
( I’m actually log shipping to a separate server for just this type of reason, I don’t suggest you break your DR log shipping )

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 2):

USED pages: Changed from (10435) to (11025) pages.

RSVD pages: Changed from (10441) to (11026) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 3):

USED pages: Changed from (10457) to (11884) pages.

RSVD pages: Changed from (10466) to (11891) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 4):

USED pages: Changed from (10449) to (11388) pages.

RSVD pages: Changed from (10458) to (11395) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 5):

USED pages: Changed from (12719) to (14058) pages.

RSVD pages: Changed from (12729) to (14066) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 38):

USED pages: Changed from (12730) to (13859) pages.

RSVD pages: Changed from (12738) to (13874) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 45):

USED pages: Changed from (20121) to (21955) pages.

RSVD pages: Changed from (20129) to (21962) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 46):

USED pages: Changed from (21831) to (23740) pages.

RSVD pages: Changed from (21841) to (23746) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 1):

RSVD pages: Changed from (280893) to (280899) pages.

Make sure you set output to text rather than grid, and make sure you save the results as this is non repeatable for this instant in time.

So here’s the results from a real table ( with it’s name changed ) what we’re interested in here is the increase in pages, index 45 has jumped by 1,834 pages , or 14.4Mb in size, now if this is only a few hours after the index was rebuilt this could be worrying..
The index in question contains two foreign keys, two dates and a bit, this probably indicates this index suffers from batch inserts which will force page overflows
A change of index key order ( assuming it doesn’t stop the index being used ) may help, e.g. swap first two keys.
Or more likely a fill factor.
We can calculate the key width at 25 bytes + the clustered key which is an int in this case + the key overhead.
Dividing table rows by pages would give the change of density, here we’ve got around 208 entries per page, with the index rebuilt this figure is around 230, it could be that an 80% fill factor might give us some relief for this index.

Use this query to find your index:- select * from dbo.sysindexes where id=object_id('SampleTable') and indid = 45

You could write a nice query to list the index columns but for now we can look in our log shipped database to find out what the columns are.
I haven’t gone into too much detail and sadly this doesn’t resolve our problems but hopefully it may help with some diagnosis and remedial work.
The techniques will work unchanged on SQL 2005 too.
Apolgies for part strange formatting - it all looks perfect until you hit publish then your entire post gets reformatted!!

Posted by GrumpyOldDBA with no comments

Filed under: Diagnostics, General Tuning, Indexes

09 June 2008 18:54

Dates - especially when SQL Server can't be British!

Just like memory config dates never seem to go away and I've had some discussions with our BI team recently on why things were not working quite as expected, especially on the 13th of the month; and I noticed the same issue had arisen for a member of the UK SSUG - Anyway rather than spend time writing a doc for the guys here Tibor has a really good post on this http://www.karaszi.com/SQLServer/info_datetime.asp it's really good paper and I'd advise anyone to print it out and add it to their essential SQL Server documents.

I actually find US English really annoying, it's our language ( British ) not theirs and I think US English should be a subset of English, not the other way around, I also get irritated that o/s installs always default to the US, and I get really really irritated when people build servers for me and leave them in the US or elsewhere - web farms with mixed locales can give some really strange results - you can use profiler to track the set options to make sure they're all the same btw.

Posted by GrumpyOldDBA with no comments

Filed under: Diagnostics, Best Practice, operating systems

26 May 2008 15:07

Covering Clustered Indexes

I’ve noticed that of late I’ve become a bit more critical of a well known publication that I suspect many DBA’s read. I have subscriptions to a number of publications and for the ones that I pay for I’m generally quite content.

I like paper / hard copy because I can make use of time where it’s just not practical to have a laptop or PC – and I spend my working day in front of a screen so sometimes it’s nice to escape to the garden or the pub or on the train, say travelling to a SSUG meeting without a PC.

So what’s set GrumpyOldDba off now you may ask? Well there’s an article been published about removing duplicate indexes – the Microsoft guys blogged this, with code, ages ago and I spent some time looking at this type of analysis as part of my index analysis series.

However it’s not quite so easy as it sounds and leaving aside duplicated single column non clustered indexes, which are obviously bad, the actual matter of where indexes have the same columns but in different orders or contained within is quite a complex matter and worth a whole lot of time on its own.

( Here’s the original blog from June 2007 http://blogs.msdn.com/mssqlisv/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx)

Idx_anytable_index1	SalesID	PropertyID	SaleStartDate	SaleEndDate	UserID	IsCurrent
Idx_anytable_index2	SalesID	UserID	PropertyID	SaleStartDate	SaleEndDate	IsCurrent
Idx_anytable_index3	SalesID	PropertyID	SaleEndDate	IsCurrent
Idx_anytable_index4	PropertyID	SalesID	SaleStartDate	SaleEndDate	UserID	IsCurrent

Taking the above indexes to be all standard non clustered indexes we can say that there’s a fair bit of duplication and in fact index 3 is already contained within index 1

Sadly with anything like this it just depends, and generally I’d say that index 3 is probably unwanted – however it may cover a query exactly, in which case index 1 might be ignored, and in certain joins one index may actually be preferred over another.

So what I’m saying is that other than basic duplicates it’s actually quite tricky, obviously you can check the dmv’s ( sql 2005 onwards ) to actually see if the index is being used, if you’re using 2000 or 7.0 then you can’t.

So what really got me worked up was the statement that creating a secondary index over a clustered index was a bad idea, now to me that such an article appears in this magazine is very disappointing and I’m also disappointed with the author – to me it shows a lack of editorial knowledge and like the review of the backup utilities last month isn’t what I’d expect from such a respected magazine. I’m also frankly becoming less and less impressed by the fact that many articles are only partly published and you have to go to the website for the full article. Maybe it’s a sign of the times that we don’t actually want paper copy any more.

Anyway here’s a very simple example of secondary indexes over the top of clustered indexes. The table I used has 2 million rows and is just under 16gb in size ( Let’s do real world examples here <grin> ) It’s a variation of the table in used in “Analysing Index Part 4 – Size does matter”

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

This is the test table and the code used to populate it. You might want to find other ways of populating the table if you’re not running on a server as I figure this will run for a few hours on a laptop! There were distinct reasons why I wanted to generate data this way for some previous tests, the only important factor for this tests is that each row should exceed half a page so as to force one row per page.

create table dbo.TestTable

(

NumKey int identity(1,1)not null,

cGuid uniqueidentifier default newid()null,

cSguid uniqueidentifier default NEWSEQUENTIALID() not null,

cBig bigint null,

cInt int null,

cDate1 datetime not null,

cDate2 datetime null,

cVchar1 varchar(50) null,

cChar1 char(20) null,

cVchar2 varchar(500) null,

cChar2 char(100),

Pad1 char(4000) not null

);

go

--

set nocount on

--

declare @num bigint,@count int,@count2 int,@count3 int

set @count = 1

set @count2 = 12

set @count3 = 1

while @count<2000001

begin

IF (@count%2)=0

begin

insert into dbo.TestTable(cBig,cInt,cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,pad1)

values( 36854775807+(36854775807/@count),@count2*3,dateadd(dd,@count3,'25 MAY 2007'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate()+@count)+datename(month,getdate()+@count)) +'blahblahblahblahblahblahblahblahblahblahblahblah',

replicate(convert(char(36),newid()),110))

end

else

begin

insert into dbo.TestTable(cBig,cInt, cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,pad1)

values( 854775807-(36854775807/@count),@count2*2,dateadd(dd,@count3,'5 dec 2006'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate())+datename(month,getdate())) +'blahblahblahblahblahblahblahblahblahblahblahblah',

replicate(convert(char(36),newid()),110))

end

set @count = @count+1;

set @count2 = @count2+2;

IF @count2>50

set @count2 = 12;

IF (@count%3000)=0

set @count3 = @count3+1;

end

Ø Here’s the output from sp_spaceused

Ø and from sys.dm_db_index_physical_stats

Ø Here’s my table after adding a clustered primary key on the integer identity column, NumKey

Ø Extracting index information from sys.dm_db_index_physical_stats

select index_id,index_type_desc,index_depth,index_level,page_count,record_count,min_record_size_in_bytes, max_record_size_in_bytes,avg_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id(), object_id('testtable'), null ,null ,'detailed' )

And the results

index_id	index_type_desc	index_depth	index_level	page_count	record_count	min_record_size_in_bytes	max_record_size_in_bytes	avg_record_size_in_bytes
1	CLUSTERED	4	0	2000000	2000000	4577	4577	4577
1	CLUSTERED	4	1	3218	2000000	11	11	11
1	CLUSTERED	4	2	8	3218	11	11	11
1	CLUSTERED	4	3	1	8	11	11

Grumpy Old DBA

This Blog

Syndication

Search

Tags

Navigation

Archives

Cannot generate SSPI context

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

Performance Dashboard for SQL 2008

Tracking problem indexes in SQL 2000

Dates - especially when SQL Server can't be British!

Covering Clustered Indexes