Indexing Help

[HestonJames]

Good afternoon guys,

I'm looking for some advice on indexing a couple of tables. These tables currently have no indexing on them and the number of records is growing substantially now performance on our report queries are slowly getting worse and worse.

Indexing is a very new topic to me and as I'm very much a 'learn by example' kind of guy thought I would get your advice on this particular scenario.

I've enclosed two queries, one showing the INSERT for the table, another showing a pretty common SELECT type statement which we would use, for which I've attached an execution plan.

Select	c.yyyy,
		c.mm,
		h.hhname,
		Sum(Case When d.LogClassName = 'org.openobex.TransferComplete' Then 1 Else 0 End ) As Success,
		Count(Distinct d.Device_ID) As UniqueDevices,
		Sum(Case When d.MinDate Is NULL Then 0 Else 1 End) As FirstAppearanceCount
From	Calendar As c
Cross Join Hours As h
Left Outer Join (
				Select	DATEADD(hh,DATEDIFF(hh, 0, MessageLog.LogDateTime), 0) As LogDate,
						MessageLog.Device_ID,
						LogClass.Name As LogClassName,
						FirstAppearance.MinDate
				From	MessageLog
				Inner Join LogClass
				On		LogClass.LogClass_ID = MessageLog.LogClass_ID
				Left Join (
							Select	Device_ID, 
									Min(LogDateTime) As MinDate
							From	MessageLog
							Inner Join LogClass
							On LogClass.LogClass_ID = MessageLog.LogClass_ID
							Where LogClass.Name In (
													'org.openobex.TransferComplete', 
													'org.openobex.Error.ConnectionRefused', 
													'org.openobex.Error.Forbidden', 
													'org.openobex.Error.NotAuthorized', 
													'org.openobex.Error.ConnectionTimeout'
													)
							And	MessageLog.ThinkTank_ID = 8
							Group By	Device_ID
				) As FirstAppearance
				On MessageLog.Device_ID = FirstAppearance.Device_ID
				And MessageLog.LogDateTime = FirstAppearance.MinDate
				Where	LogClass.Name In (
										'org.openobex.TransferComplete', 
										'org.openobex.Error.ConnectionRefused', 
										'org.openobex.Error.Forbidden', 
										'org.openobex.Error.NotAuthorized', 
										'org.openobex.Error.ConnectionTimeout'
										)
				And		MessageLog.LogDateTime >= '20081015'
				And		MessageLog.LogDateTime < '20081016'
				And		ThinkTank_ID = 8
				) As d
				On d.LogDate = dateadd(hh, h.hh, c.caldate)
Where	c.caldate >= '20081015'
And		c.caldate < '20081016'
Group By	c.yyyy,
			c.mm,
			h.hhname
Order By	c.yyyy,
			c.mm,
			h.hhname

and

					INSERT
					  INTO MessageLog
					     ( Device_ID
					     , LogDateTime 
					     , LogClass_ID
					     , Media_ID   
					     , Campaign_ID
					     , ThinkTank_ID )
					SELECT Device.Device_ID
					     , ##MessageStaging.LogDateTime
					     , LogClass.LogClass_ID
					     , ##MessageStaging.Media_ID
					     , ##MessageStaging.Campaign_ID
					     , 41
					  FROM ##MessageStaging
					INNER
					  JOIN LogClass
					    ON LogClass.Name = ##MessageStaging.LogClass
					INNER
					  JOIN OUI
					    ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress,8) 
					INNER
					  JOIN Device
					    ON Device.OUI_ID = OUI.OUI_ID
					   AND Device.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8)

Can you offer your recomendations on which columns should be indexed here? and what type of index I should be using on those columns?

One thing worth mentioning is that the INSERT statements are actioned from automated tasks or remote client applications and NOT by users, so performance on those isn't so important, the SELECT however is for a reporting tool which has user interaction so performance is quite important and at the moment its just being killed and causing me user experience problems :-(

Thank you all guys, if you can make some suggestions I can action them onto the database and try some testing.

Cheers all,

Heston

 

[gmmastros]

My best advice on this is to let SQL Server do the heavy lifting.

Load the query in to a SQL Server Management Studio window.
Click Query -> Analyze Query in Database Engine Tuning Advisor.

A new window will open (eventually). On the menu bar, there is a 'Start Analysis' button. Most likely, it will present some index recommendations that you can apply (if you want to).

You need to be careful about applying too many indexes to your tables. Indexes are great for speeding up select queries, but they slow down Inserts, Updates, and Deletes because the indexes need to be maintained.

Indexes are a balancing act that only you can do. If your database is primarily used for reporting, then you can afford to have more indexes. If your database has a lot of transactions (Insert/Update/Delete), then you want less indexes. What's the right number? I don't know. Ideally, you want everything to be fast. So the only way to be sure is to test performance after adding an index. If an index is unacceptable, simply remove it.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EKOnerhime]

Not a direct answer to your question BUT there is a huge amount of indexing resource out there, but this I found to be a gem.

The Ultimate Missing Index Finder by Jesse Roberge.

http://www.sqlservercentral.com/scripts/Index+Management/63937/

 

[HestonJames]

Hey George,

Thats some very sound advice, thank you. I had read about the performance of transactions on the data, in this instance we insert data on a regular basis but we never modify or delete data, we also select and report on a regular basis, also, the selects are always dealing with much much larger sets of data so I think I'll likely lean towards performance tuning for that eventuality :-D

I've not used the query analysis stuff before, that sounds very promising, I'll try running my way through that and see what it suggests to me :-D

I'll keep you updated on what I see in my testing.

@ EKOnerhime - Thank you for that article, I'll be sure to give it a thorough read through and see what it suggests :-D

Many thanks again guys.

Heston

 

[HestonJames]

George,

I cant find the query tuning stuff anywhere on my system (running SQL Server 2k5)... its not in the 'query' menu nor is it listed as a seperate app in start > all programs > sql server > performance tools etc etc.

Is this a feature which needs to be installed additionaly? or is it just hiding somewhere?

Cheers,

Heston

 

[gmmastros]

I don't know. I don't recall selecting any additional options when I installed SQL Server Management Studio, but maybe I did. Try...

Open the query in a new query window.
Right click in the query window.
You should have a menu item for 'Analyze Query in Database Engine Tuning Advisor'.

Alternatively, search your computer for a file named: DTASHELL.EXE

On my computer, it's at...
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\DTASHELL.EXE"

I'm running Vista 64 Bit, so I have 2 "Program Files", one with the (x86) added to it (for 32 bit apps). So you may also find yours at:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\DTASHELL.EXE"

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

George,

Thanks, I've tried both of those options and niether worked, I get no listing like that on the query window and that file doesnt seem to exist iether.

This is both true on the copies I have installed on my Vista development machine and my 2k3 production box... very stange.

I'll try reinstalling the management studio and see if it lists it as an option somewhere.

Heston

 

[MDXer]

Remember the Index Tuning Advisor will not always find every needed index or fully optimize. I recently had a query doing a table scan the Advisor recommended 1 index which resulted in a clustered index scan. Running it with this index in place resulted in no reccomendations. I was able to add an additional index that moved my query to use an Index Seek. The difference was 10+ minutes versus < 2 minutes.

 

[HestonJames]

MDXer, thats very interesting, I'll treat the analysis results as a starting point and test and move from there. I do quite regularly see queries running close to 10 mins so it'll be interesting to see how much more performance I cant make it.

Heston

 

[gmmastros]

http://www.microsoft.com/Sqlserver/2005/en/us/Compare-Features.aspx

In the 'Manageability' section, it shows that the Database Tuning Advisor is not available for the Express or WorkGroup edition.

Alternatively, you could purchase a developer edition, which has all the same features as the Enterprise version.

http://www.microsoft.com/sqlserver/2005/en/us/developer.aspx

Apparently, developer edition costs costs $49.

http://www.microsoft.com/sqlserver/2005/en/us/pricing.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I agree with MDXer. The tuning advisor won't always help. But it is a great place to start.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Ah, good old M$, this'll be because I'm running workgroup edition at the moment.

Right, off to find myself a keygen ;-)

Cheers George, nice find.

Heston