Why did it take 9 seconds to execute query.

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

General SQL Server Forums

New to SQL Server Programming

Why did it take 9 seconds to execute query.

Author

Topic

Jay123
Yak Posting Veteran

54 Posts

Posted - 2010-01-27 : 05:04:38

Why did it take my computer 9 seconds to execute the qurey stated below. i have done it on my old machine (which has half the specs of this one) and it was instant. so why so long on this comp.

Create Database Marketing
on Primary
(name = Marketing_Data,
FileName = 'C:\Marketing\Marketing_Data1.MDF',
Size = 50MB,
MaxSize = 60MB,
FileGrowth = 10%),

FileGroup MarketingFG1
(name = Marketing_Data1,
Filename = 'c:\Marketing\Marketing_Data1.ndf',
Size = 60MB,
MaxSize = 70MB,
FileGrowth = 10%),
(name = Marketing_Data2,
FileName = 'c:\Marketing\Marketing_Data2.ndf',
Size = 40Mb,
MaxSize = 50MB,
FileGrowth = 20%),

FileGroup Marketing_HistoryFG
(Name = marketing_History,
FileName = 'c:\Marketing\Marketing_History1.ndf',
Size = 70MB,
MaxSize = 80MB,
FileGrowth = 21%)

Log on
(name = Marketing_Log,
FileName = 'c:\Marketing\Marketing_Log.ldf',
Size = 10MB,
MaxSize = 20MB,
FileGrowth = 10%)

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 05:32:14

Compatibility mode not maximum for server / SQL version

Tables not recently re-indexed

UPDATE STATISTICS not run recently, or run using a small sample rather than WITH FULLSCAN

other than that need to look at the Query Plan to see where they are different.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-27 : 07:18:55

Disk Layout should be efficiently designed. You should separate Mdf in Separateb Drive (RAID 5 or RAID 10) and Ldf file in Write intensive Drives(RAID 10).

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 07:28:02

Hmm ... in that vein Backups to separate drive, and TEMPDB on separate drive too ... if possible.

Jay123
Yak Posting Veteran

54 Posts

Posted - 2010-01-27 : 07:42:11

sorry i should have been clearer.

i am on my laptop doing college work.

i only had one other uddb which contains 2 words.

how do i get Query Plan .

my specs are:

Processor:

type : Intel® Core™2 Duo processor T6600
clock speed : 2.20 GHz
Front Side Bus : 800 MHz
2nd level cache : 2 MB

Windows 7

System memory:
standard : 4,096 (2,048 + 2,048) MB
maximum expandability : 8,192 MB
technology : DDR2 RAM (800 MHz)

Hard disk:
capacity : 320 GB
drive rotation : 5,400 rpm

Thanks... Jay

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 07:48:48

"how do i get Query Plan ."


SET SHOWPLAN_TEXT ON
GO

... put query here ...

SET SHOWPLAN_TEXT OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources