May 20, 2015 at 10:06 am
Hello all -
I am application programmer but have inherited a SQL Server (Enterprise) database with most of the tables working well except one - to get a select top(10) * from [the_table] usually takes over 30 seconds. I have updated the stats, helped briefly but the table is back to running very slow. Other tables int eh same database are performing well and quickly but this one.
Here is the structure and index scheme for the table which only has 3,900 rows but is otherwise very large rows...
CREATE TABLE [dbo].[GSIM_Data](
[id] [uniqueidentifier] NOT NULL,
[idsid] [nchar](50) NULL,
[Description] [nvarchar](150) NULL,
[Command] [nvarchar](2500) NULL,
[Status] [nvarchar](50) NULL,
[GxxxItem_ID] [int] NULL,
[Product] [nchar](10) NULL,
[Submit_Date] [datetime] NULL,
[FilePath] [nvarchar](255) NULL,
[Stats_Data] [varbinary](max) NULL,
[Stats_Data_Small] [varbinary](max) NULL,
[Output_File] [nvarchar](100) NULL,
[Output_Dir] [nvarchar](255) NULL,
[Axxx_Command] [nvarchar](2500) NULL,
[Axxx_Output_File] [nvarchar](100) NULL,
[Axx_Output_Dir] [nvarchar](255) NULL,
[Axxx_Data] [varbinary](max) NULL,
[Launch_Date] [datetime] NULL,
[Finish_Date] [datetime] NULL,
[Note] [nvarchar](max) NULL,
[Run_Axxx_Only] [nvarchar](10) NULL,
[Axxx_ID] [int] NULL,
[Gxxx_Input_Dir] [nvarchar](255) NULL,
[Axxx_Input_Dir] [nvarchar](255) NULL,
[Grxxx_Command] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INDEX STRUCTURES
CREATE UNIQUE CLUSTERED INDEX [ciGxxx_Data_ID] ON [dbo].[Gxxx_Data]
([id] ASC
)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 = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [Gxxx_Data_notes_idx] Script Date: 5/20/2015 8:57:50 AM ******/
CREATE NONCLUSTERED INDEX [Gxxx_Data_notes_idx] ON [dbo].[Gxxx_Data]
([Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
Here's what the storage tab looks like
ROWCOUNT 3976
Index space 0.344 Mb
Data space 20,529.068 Mb (20Gb)
I am reading Grant's book but not sure I understand the forces at work in this scenario but need to solve/significantly improve quickly
Thanks in advance for you help and suggestions
Tom
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 10:47 am
If you actually use a list of columns and leave out the (max) columns, does it come back quickly? It could be that the volume of data you actually bring back with those first 10 rows is MUCH larger than the volume of data you are getting back from the same query on any other table.
May 20, 2015 at 11:10 am
20 GB seems rather large for a table with 4000 rows even with the max data types, and especially since your clustered index and 1-column non clustered index is taking only .3mb of storage. Something about that does not add up.
Anyhow, can you attach a copy of your query plan? Also, is there an ORDER BY clause in your SELECT statement?
-- Itzik Ben-Gan 2001
May 20, 2015 at 11:16 am
SELECT TOP 10 without any kind of ORDER BY or WHERE clause? If so, that's going to result in an index scan. That could be the issue right there. Having a WHERE clause makes a difference. Otherwise, the only thing you can tune is to tune the hardware.
Sorry the book isn't proving helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2015 at 11:44 am
When a table scan occurs, do the pages read equate to the total 20 GB size of the table? If so, then that explains why a table scanning query would perform so poorly. Considering the ratio of blob data to regular key and attribute columns, you will probably want to insure that text data is stored off-row.
The following will tell us more about the storage organization of this table. Just plug in your actual table name.
select
schema_name(o.schema_id)schema_name,
o.name obj_name,
i.name idx_name,
i.type_desc idx_type,
ps.alloc_unit_type_desc,
ps.record_count,
cast(ps.avg_record_size_in_bytes as smallint)avg_recordsize_bytes,
((ps.page_count*cast(8 as bigint))/1024) size_mb,
ps.ghost_record_count,
ps.forwarded_record_count,
ps.compressed_page_count,
isnull(u.user_seeks + u.user_scans + u.user_lookups + u.system_seeks + u.system_scans + u.system_lookups,0) read_count,
isnull(u.user_updates + u.system_updates,0) write_count,
fill_factor,
is_padded,
text_in_row_limit,
cast(ps.avg_fragmentation_in_percent as tinyint)avg_frag_pct,
cast(ps.avg_page_space_used_in_percent as tinyint)avg_pageused_pct
from sys.dm_db_index_physical_stats
(DB_ID(), object_id('<Actual Table Name>'), NULL, NULL , 'SAMPLED') as ps
join sys.tables o on ps.object_id = o.object_id
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
join sys.partitions p ON ps.object_id = p.object_id and ps.index_id = p.index_id
left join sys.dm_db_index_usage_stats u on u.object_id = ps.object_id and u.index_id = ps.index_id
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 20, 2015 at 12:29 pm
Grant Fritchey (5/20/2015)
SELECT TOP 10 without any kind of ORDER BY or WHERE clause? If so, that's going to result in an index scan.
Shouldn't be too much of a problem. Sure, it's an index scan but it's an index scan that will only read 10 rows (or more correctly the SELECT will only ask it for a row 10 times)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 12:37 pm
GilaMonster (5/20/2015)
Grant Fritchey (5/20/2015)
SELECT TOP 10 without any kind of ORDER BY or WHERE clause? If so, that's going to result in an index scan.Shouldn't be too much of a problem. Sure, it's an index scan but it's an index scan that will only read 10 rows (or more correctly the SELECT will only ask it for a row 10 times)
Each row is an average of 5 MB, so assuming SQL Server read the complete row, including text columns, that would be 50 MG. In that case, perhaps 30 seconds for a return to client makes sense.
Here's what the storage tab looks like
ROWCOUNT 3976
Index space 0.344 Mb
Data space 20,529.068 Mb (20Gb)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 20, 2015 at 1:06 pm
Eric M Russell (5/20/2015)
GilaMonster (5/20/2015)
Grant Fritchey (5/20/2015)
SELECT TOP 10 without any kind of ORDER BY or WHERE clause? If so, that's going to result in an index scan.Shouldn't be too much of a problem. Sure, it's an index scan but it's an index scan that will only read 10 rows (or more correctly the SELECT will only ask it for a row 10 times)
Each row is an average of 5 MB, so assuming SQL Server read the complete row, including text columns, that would be 50 MG. In that case, perhaps 30 seconds for a return to client makes sense.
Sure, I'm just saying that a TOP without a WHERE or ORDER isn't automatically a problem because it won't read the entire table. If the rows are huge, yeah that's going to be slow because the rows are huge.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 2:16 pm
Yes, limited columns gives much better performance
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 2:21 pm
Hi Alan - attached is the actual query plan - looks ok. As noted earlier when I limit the columns required the performance is fine - just select * is painfully slow
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 2:26 pm
Here is the result form the query you sent - thank you
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 2:31 pm
Thanks Gail - as you noted with/without where clause was almost no difference in this case. It seems to be just the volume of data per row.
Although I don't quite understand how yet, it sounds like there is a smarter way of storing the large fields that 'in the row' that will help keep the performance good.
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 2:31 pm
Tom_Sacramento (5/20/2015)
Hi Alan - attached is the actual query plan - looks ok. As noted earlier when I limit the columns required the performance is fine - just select * is painfully slow
It's the LOB columns then. Not uncommon, they're stored out of row, they're often slow to retrieve, especially if they're large.
Do you need to do SELECT *? Do you need the LOB columns when the table is queried?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 2:33 pm
Hi Grant - I should rephrase my statement - the book has been very helpful and elucidating - just could not correlate this scenario with the book so I needed to ask for help. Thank you
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2015 at 2:34 pm
Tom_Sacramento (5/20/2015)
Yes, limited columns gives much better performance
It looks like the results are for a handful of tables in MASTER database. When running the script, change context to database containing your table, and then specify your table name 'GSIM_Data'.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply