Trying to find cause of slowness

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 2008 Forums

SQL Server Administration (2008)

Trying to find cause of slowness

Author

Topic

schmidtg
Starting Member

36 Posts

Posted - 2009-07-22 : 14:49:25

Hello,
I just installed a new instance of SQL Server 2008 on Windows Server 2008 and am seeing extreme slowness. I have a large table with ~3 million records and even running SELECT COUNT(1) FROM Things takes nearly a minute. This is a pretty powerful machine, so I don't think it's hardware limitations. Any advice on what I might do to figure out the cause of slowness? Thanks in advance.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-07-22 : 15:14:54

tell me about how you went about moving the database onto your new instance? Did it perform this way before? Did you create the db and then import the records? The reason I ask is it sounds like maybe you're missing indexes, statistics are out of date perhaps?

Mike
"oh, that monkey is going to pay"

schmidtg
Starting Member

36 Posts

Posted - 2009-07-22 : 15:19:17

I created the table from scratch and populated it from a program. There was no previous instance to compare performance with. Perhaps this is expected behavior? I just thought a basic SELECT COUNT(*) should be quicker than this. Here's my scripted table in case it provides any insight:

CREATE TABLE [dbo].[RawStatuses](
[RawStatusID] [bigint] IDENTITY(1,1) NOT NULL,
[Status] [nvarchar](max) NULL,
[DateRetrieved] [datetime] NULL,
[Parsed] [bit] NULL,
CONSTRAINT [PK_RawStatuses] PRIMARY KEY CLUSTERED
(
[RawStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RawStatuses] ADD CONSTRAINT [DF_RawStatuses_DateRetrieved] DEFAULT (getdate()) FOR [DateRetrieved]
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:16:15

rebuild the indexes after populating it.

to see the count(*), this is faster

SELECT rows FROM sys.partitions where object_id = object_id('RawStatuses');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources