Best Way To Increase Speed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Best Way To Increase Speed

Author

Topic

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 11:55:08

I got a problem with one of my tables here are some specs on it:

tblHits

Data Space: 608.227 MB
Filegroup: PRIMARY
Index Space: 308.242 MB
Row Count: 2,103,232
Table is partitioned: False

This is running on MS SQL Server 2005

What is the best practice to increase speed of this table, is there a way to partition the data so that it's split up into two tables? is there a way to shrink it?

Thanks for your help.

Ado

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:03:35

"What is the best practice to increase speed of this table"

Tune it.

Show us a query that is running slowly, and the Query Plan for it, and some DDL and I expect folk here will give you suggestions.

Just assuming you need to Partition the table is a very blunt-instrument type approach !

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 12:22:33

Here is some more info about the table:

Table Structure:

ID - Primary Key (Is Identity) (bigint)
Campaigns_ID (int)
bolFirstHit (tinyint)
Paid (tinyint)
uniqueID (varchar(25))
IP (varchar(16))
crcookie (int)
crsession (int)
soruce (varchar(250))
crkeywords (varchar(200))
landingpage (varchar(450))
fullrefering (varchar(650))
guid (varchar(100))
datetime (datetime)

example of a query that will time out:

SELECT uniqueID FROM tblHits WHERE DateTime >= '1/10/2010' AND DateTime <= '1/27/2010' AND Campaigns_ID=2880 AND crkeywords=''

Hope this helps.

Regards,

Ado

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:29:20

Yup, that's a start, thanks.

Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats


-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

*** put query here ***

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO

Need to see what the Scans and LOGICAL I/O is. Any changes made that reduce those will indicate that they are improvements (to the baseline).

Also need the Query Plan pls.

(Comment in and run once for each; the STATS one will give you actual output from your query (in case that is millions of rows!!!) the PLAN one won't give you output from your query)

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:30:41

P.S. I'll put my Dollar on "AND crkeywords=''" being a culprit!!

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 12:43:23

Here is what I got with that:


SET STATISTICS IO ON;
 SET STATISTICS TIME ON
 SELECT uniqueID FROM tblHits WHERE DateTime >= '1/10/2010' AND DateTime <= '1/27/2010' AND Campaigns_ID=2880 AND crkeywords=''

  |--Filter(WHERE:([SQL_Boost].[dbo].[tblHits].[datetime]>='2010-01-10 00:00:00.000' AND [SQL_Boost].[dbo].[tblHits].[datetime]<='2010-01-27 00:00:00.000'))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([SQL_Boost].[dbo].[tblHits].[ID], [Expr1006]) WITH UNORDERED PREFETCH)
            |--Merge Join(Inner Join, MERGE:([SQL_Boost].[dbo].[tblHits].[ID])=([SQL_Boost].[dbo].[tblHits].[ID]), RESIDUAL:([SQL_Boost].[dbo].[tblHits].[ID] = [SQL_Boost].[dbo].[tblHits].[ID]))
            |    |--Index Seek(OBJECT:([SQL_Boost].[dbo].[tblHits].[tblHits_Campaigns_ID]), SEEK:([SQL_Boost].[dbo].[tblHits].[Campaigns_ID]=(2880)) ORDERED FORWARD)
            |    |--Index Seek(OBJECT:([SQL_Boost].[dbo].[tblHits].[tblHits_crkeywords]), SEEK:([SQL_Boost].[dbo].[tblHits].[crkeywords]='') ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([SQL_Boost].[dbo].[tblHits].[PK_tblHits]), SEEK:([SQL_Boost].[dbo].[tblHits].[ID]=[SQL_Boost].[dbo].[tblHits].[ID]) LOOKUP ORDERED FORWARD)


 SET STATISTICS IO OFF;
 SET STATISTICS TIME OFF

After I ran the same thing without the "AND crkeywords=''" i got this for StmtText

  |--Filter(WHERE:([SQL_Boost].[dbo].[tblHits].[datetime]>='2010-01-10 00:00:00.000' AND [SQL_Boost].[dbo].[tblHits].[datetime]<='2010-01-27 00:00:00.000'))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([SQL_Boost].[dbo].[tblHits].[ID], [Expr1006]) WITH UNORDERED PREFETCH)
            |--Index Seek(OBJECT:([SQL_Boost].[dbo].[tblHits].[tblHits_Campaigns_ID]), SEEK:([SQL_Boost].[dbo].[tblHits].[Campaigns_ID]=(2880)) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([SQL_Boost].[dbo].[tblHits].[PK_tblHits]), SEEK:([SQL_Boost].[dbo].[tblHits].[ID]=[SQL_Boost].[dbo].[tblHits].[ID]) LOOKUP ORDERED FORWARD)

I also ran the two queries and they take around the same amount of time to load.

What do you mean by the Query Plan?

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:56:33

"What do you mean by the Query Plan?"

That's the Query Plan that you've given me

What is missing is the STATISICS bit. You need to comment-in only one of the two lines at the top of my code - looks like you commented both in.

Looks like you don't have an index on [datetime]? (Well, if you do it isn't being used) This query would probably benefit from an index on [datetime]

[crkeywords]='' is using the index, so I'll have to eat my hat on that one!

Are the indexes rebuilt regularly, and the Statistics Updated? If not that would lead to appalling performance, over time, if significant amounts of data are being added.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-26 : 14:05:43

I'm always tempted to answer "sp_configure 'Run SQL Faster',1" to performance questions like this...

Ado, since this is SQL 2005, can you upload the graphical plan somewhere and post a link? There's details on how in this article - [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

--
Gail Shaw
SQL Server MVP

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 14:07:35

I didn't have datetime indexed, I just set it up and it's going a lot faster.

I also didn't have any Maintenance plan setup on the table to reindex so the index on it was only run once when it was originally indexed which was months ago.

Thank you so much for your help. I really started wondering weather I needed to start archiving data.

Do you think archiving data is good? I've never had any experience with maintaining databases. Do you have any suggestions on where I may be able to find more information about proper database maintenance?

Once again thank you so much for your help.

Ado

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 14:25:56

Well ... you'll need to archive eventually, so starting soon is better than trying to solve what the problem once it has become a multi-terabyte problem!

But 2,000,000 rows is not a lot.

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 14:31:03

Very interesting, I just did the exec plan it does provide a lot of the information thanks for the info. I will be sure to include a lot more info on my future questions.

Ado
Starting Member

6 Posts

Posted - 2010-01-26 : 14:32:01

Kristen,

Got it. Thx.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 14:41:23

"I just did the exec plan it does provide a lot of the information thanks for the info"

Well at least you know about that now. Scary the number of non-SQL-DBA DEVs who knock out queries without knowing how to check the query plan etc. No need to understand it in any great detail (I'm sure I don't!) just look at which indexes are being used and see if they seem sensible from a laymans perspewcitgve. If the PK is being used for the [DateTime] test that is because the PK is clustered, and its the only way to walk through the data!

Aim that all the indexes are Index SEEK and not Index SCAN. And you definitely don't want to see any Table SCAN in there.

That's about it. Beyond that come back and ask.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources