February 19, 2010 at 1:09 pm
I have a table, in an SQL2005 database, which is used to store searches that people do on a website.
The table is 'standalone' - it has no foreign keys, and no other table references it.
According to its Properties dialog, it has 1,849,982 rows.
When I try and do a select from it, it behaves very strangely.
If I try:
select top 100000 * from search_data order by search_data_id asc
It returns the 100,000 rows in less than a second. If I increse the number of rows to 2-300,000 (it varies form query to query) it just sits there and doesn't return any values.
If I try:
select top 100 * from search_data order by search_data_id desc
It just sites there and doesn't retun any values (nor does it timeout, nor does it return an error)
Same result if I try
select count(*) from search_data
The table has 2 indexes, which have both been rebuilt, with plenty of space (the table gets lots of additions, so I keep plenty of space for expansion).
The table is quite simple and is defined as:
CREATE TABLE [dbo].[search_data](
[search_data_id] [int] IDENTITY(1,1) NOT NULL,
[keywords] [varchar](500) NOT NULL,
[results] [int] NOT NULL,
[created_date] [smalldatetime] NOT NULL,
CONSTRAINT [PK_search_data] PRIMARY KEY NONCLUSTERED
(
[search_data_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IX_search_data] ON [dbo].[search_data]
(
[keywords] ASC,
[created_date] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
ALTER TABLE [dbo].[search_data] ADD CONSTRAINT [DF_search_data_results]
DEFAULT (0) FOR [results]
ALTER TABLE [dbo].[search_data] ADD CONSTRAINT [DF_search_data_created_date]
DEFAULT (getdate()) FOR [created_date]
I've also run DBCC CHECKDB & DBCC CHECKTABLE, but neither reported any errors.
Restoration from backup is not an option, as we keep 14 days worth of backups and I know this error goes back longer than that.
Any ideas what is wrong and what I can do to fix it?
Thanks for any suggestions!
February 19, 2010 at 2:17 pm
Have you compared the execution plans between the different queries?
February 19, 2010 at 5:19 pm
Lynn Pettis (2/19/2010)
Have you compared the execution plans between the different queries?
Lynn,
Thanks for the suggestion. Unfortunately, the execution plan for both queries are exactly the same.
If I could post the image here I would - I can post the Execution plan XML if it would help.
Regards
Adam
February 19, 2010 at 5:47 pm
The image wouldn't help, and the xml difficult to read. Since this is SQL Server 2005, you can save the plan as a .sqlplan, zip and then upload here and that would be good. Be sure it is the actual execution plan not the estimated execution plan.
February 19, 2010 at 5:48 pm
Have you tried rebuilding the clustered index?
February 20, 2010 at 5:04 pm
Lynn Pettis (2/19/2010)
The image wouldn't help, and the xml difficult to read. Since this is SQL Server 2005, you can save the plan as a .sqlplan, zip and then upload here and that would be good. Be sure it is the actual execution plan not the estimated execution plan.
Lynn,
I've attached two execution plans. One is the actual execution plan for an order by asc, the other is for order by desc.
The order by desc query was changed to
select top 1 * from search_data order by search_data_id desc
so that it would finish.
I haven't used execution plans extensively, but there does not appear to be anything odd here.
To answer your other question, about rebuilding the indexes, both indexes have been fully rebuilt several times in trying to fix this issue.
Regards
Adam
February 21, 2010 at 9:59 am
I think I've fixed it.
I dropped and recreated both indexes (having had to drop all connections from the database to make the drop work)
Now, select * from search_data order by search_data_id desc
returns about 1.8 million rows in 10 seconds and the query I wanted to do:
select count(keywords) as frequency, keywords from search_data
where created_date > '2009-01-01'
group by keywords
order by frequency desc
to return the most popular searches since the beginning of last year, returns about 44,000 results in less than a second.
Any ideas what might have been wrong with the indexes that rebuilding them wouldn't fix, but dropping and recreating would fix?
Also, is there a simple way of testing all indexes to see if similar problems exist in other indexes?
Regards
Adam
February 21, 2010 at 4:06 pm
You might also want to check you tempdb and the Disk that it is on. One thing that might cause this is if it tried to expand tempdb, but was blocked or stalled waiting for the disk subsystem to respond.
Also, the disk that your database's log file is on.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 2:25 am
RBarryYoung (2/21/2010)
You might also want to check you tempdb and the Disk that it is on. One thing that might cause this is if it tried to expand tempdb, but was blocked or stalled waiting for the disk subsystem to respond.Also, the disk that your database's log file is on.
I don't believe this is the cause in this case, as my development server, which uses a copy of the live database from just after Christmas also displays the same problem on the same tables.
I think my best course of action now is to try and identify any other indexes that are displaying similar problems and get them recreated.
Not easy, as the drop/create process is way more intrusive to the users than a rebuild or reorganise.
Regards
Adam
February 22, 2010 at 2:33 am
OK, it was just a guess on my part.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 3:35 pm
No problem - all suggestions welcome!
February 23, 2010 at 11:55 am
I usually don't jump in on threads that have already had several answers, but I do have a couple of comments.
Your clustered index is probably not the best candidate/design for the clustered index. The clustered index should be narrow and unique. Yours is definitely not narrow with the leading column being varchar(500). By having a wide (potentially 508 bytes (4 for the autogenerated uniqueifier)) clustered index you are limiting the number of rows on the index pages for both the clustered and non-clustered indexes which will cause more IO's. Kimberly Tripp does a much better job explaining this than I do in this blog post
Based on what you have shared I'd be inclined to use the Primary Key as the clustered index and then make your clustered index non-clustered and reverse the order of the columns (created_date, keywords) or even use keywords as an INCLUDED column and not part of the key. The queries you have shared do not use the keyword column in any kind of filtering so the clustered index can't be used except in a KEY LOOKUP.
Did you rebuild or reorganize the indexes? How about updating statistics? Rebuilding the indexes will update the statistics, but Reorganize will not. SQL Server may also have created column statistics that have gotten stale and need to be updated.
I'd also be interested in seeing the results from STATISTICS IO and when the query is "hung" I'd like to see the results from a query like this:
SELECT
DEC.session_id,
DER.database_id,
DES.login_name,
DES.original_login_name,
DEC.connect_time,
DES.login_time,
DES.[host_name],
DES.program_name,
DEC.net_transport,
DEC.protocol_type,
DEC.auth_scheme,
DEC.last_read,
DEC.last_write,
DEC.client_net_address,
DER.request_id,
DER.start_time,
DER.status,
DER.command,
DER.[user_id],
DER.blocking_session_id,
DER.wait_type,
DER.last_wait_type,
DER.wait_resource,
DER.percent_complete,
DES.client_version,
DES.client_interface_name,
DES.status,
DES.memory_usage,
DES.last_request_start_time,
DES.last_request_end_time,
DES.is_user_process
FROM
sys.dm_exec_connections AS DEC JOIN
sys.dm_exec_sessions AS DES ON DEC.session_id = DES.session_id LEFT JOIN
sys.dm_exec_requests AS DER ON DES.session_id = DER.session_id;
Especially the wait type information. I'd guess it would be some type of IO Wait.
Lastly, this has nothing to do with corruption. The table is fine, you just need to do some optimization of the design and the queries. If the table or indexes was corrupt DBCC would tell you and you likely wouldn't be able to access the table at all.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply