April 11, 2009 at 10:26 am
Hi,
is possible create heap with primary key??
Im testing query... If I have clustered table that logical reads is 20696, if I have heap table without clustered index logical read is only 3.
For testing I used "set statistics io on".
Table have only 3rows than I think clustered index is not need. But I need key bacause database has diagrams.
Thanks Radek
April 11, 2009 at 10:31 am
Yes, just create your table with a Primary Key that is not clustered.
[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]
April 11, 2009 at 10:35 am
RBarryYoung (4/11/2009)
Yes, just create your table with a Primary Key that is not clustered.
Right , but how? I tried it but it every create clustered index 🙂
like this:
CREATE TABLE [dbo].[test6](
[callerid] [char](5) NOT NULL,
[name] [varchar](50) NULL,
[address] [varchar](50) NULL,
[recordnum] [int] IDENTITY(1,1) NOT NULL
primary key( callerid)
) ON [PRIMARY]
GO
thanks Radek
April 11, 2009 at 10:37 am
radek (4/11/2009)
Hi,is possible create heap with primary key??
Sure. Create the PK as a nonclustered index.
ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY NONCLUSTERED ....
Im testing query... If I have clustered table that logical reads is 20696, if I have heap table without clustered index logical read is only 3.
For testing I used "set statistics io on".
What's your query, what's the table structure in the two cases?
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
April 11, 2009 at 10:39 am
like this:
CREATE TABLE [dbo].[test6](
[callerid] [char](5) NOT NULL,
[name] [varchar](50) NULL,
[address] [varchar](50) NULL,
[recordnum] [int] IDENTITY(1,1) NOT NULL
primary key NONCLUSTERED ( callerid)
) ON [PRIMARY]
GO
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
April 11, 2009 at 10:56 am
What's your query, what's the table structure in the two cases?
Table is realy simple:
CREATE TABLE [dbo].[tbl_PRODUCT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT] [varchar](10) NOT NULL
) ON [PRIMARY]
but query is difficult because in that query is much more table and view,
and view which is create from view.
I tested that query with "set statistics io on"
and try to do optimize where number is high.
I wrote that view where was clustered table "product" logical read was 20696 and heap was 3 I think much more better..
before compare I run this:
dbcc freeproccache
dbcc dropcleanbuffers
thanks Radek
April 11, 2009 at 11:07 am
Post the exec plan for each please.
While a heap is occasionally beneficial, in most cases it's better for a table to have a cluster. There's probably a missing index or bad estimate that's causing the high reads.
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
April 11, 2009 at 11:42 am
GilaMonster (4/11/2009)
Post the exec plan for each please.While a heap is occasionally beneficial, in most cases it's better for a table to have a cluster. There's probably a missing index or bad estimate that's causing the high reads.
There is plan in xml format I Use ms sql 2005 standard, file have txt format but it is xml, xml cant reload.
Thank a lot Radek
April 11, 2009 at 12:22 pm
Wow. Do you have the version of the SQLPLAN that has only 3 logical IOs?
[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]
April 11, 2009 at 12:49 pm
RBarryYoung (4/11/2009)
Wow. Do you have the version of the SQLPLAN that has only 3 logical IOs?
Yes, It is plan with 3 IO on table tbl_product_area 🙂
there is result from "statistics io on"
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 234 ms, elapsed time = 741 ms.
Table 'tbl_TOP_FAILURES'. Scan count 236, logical reads 13740, physical reads 120, read-ahead reads 376, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TOP_FAILURES_INFO'. Scan count 1, logical reads 1293532, physical reads 3, read-ahead reads 5474, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 482, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_product_name_for_pseudo_f_location'. Scan count 20, logical reads 89, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_product_group_for_pseudo_f_location'. Scan count 20, logical reads 89, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_terminal_for_pseudo_f_location'. Scan count 20, logical reads 152, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_f_location_for_pseudo_f_location'. Scan count 230, logical reads 10543, physical reads 1, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_AREA'. Scan count 230, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_GROUP'. Scan count 0, logical reads 21344, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_TYPE'. Scan count 0, logical reads 85848, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TERMINAL'. Scan count 0, logical reads 77684, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCTS'. Scan count 3643, logical reads 47532, physical reads 1, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TERM_ID'. Scan count 3853, logical reads 26948, physical reads 1, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_FAILURES_archive_only_first_entrys'. Scan count 3873, logical reads 255618, physical reads 1, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_FAILURE_CAUSE'. Scan count 3413, logical reads 20108, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 41610 ms, elapsed time = 46125 ms.
(236 row(s) affected)
SQL Server Execution Times:
CPU time = 41844 ms, elapsed time = 46866 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Result from statistics is for table where tbl_product_area was heap, when was clustered logical read was about 20000.
April 11, 2009 at 3:25 pm
I think you could probably cut way down on the scans and rowcounts if you did a little "Divide'n'Conquer" to do some pre-aggregations to temp tables and join on those.
Of course, I could be wrong. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2009 at 9:40 pm
radek (4/11/2009)
RBarryYoung (4/11/2009)
Wow. Do you have the version of the SQLPLAN that has only 3 logical IOs?Yes, It is plan with 3 IO on table tbl_product_area 🙂
Could you post that too, please.
[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]
April 11, 2009 at 11:15 pm
Hey radek,
I saved that XML as a sql plan and loaded it in SSMS.
:blink: :blink: :blink: :blink: :blink: :blink: :blink:
{mode = glass-half-full}
I think those tables could do with some useful indexes. You might also like to take a close look at some of the data types - there seem to be a few implicit conversions from strings to datetimes for example.
It should be relatively easy to improve this procedure's performance. :Whistling:
/Paul
P.S. For those who may be too afraid to open the full plan, just imagine *lots* of scans feeding into hash joins. Lots.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 4:42 am
I suspect the cluster vs heap discrepancy is more the result of a lot of bad and missing indexes.
It would really help if you could post the actual execution plan. The one that you gave us is an estimated plan and doesn't have the actual row counts. since bad estimates can result in really bad plans it would be useful to see both the estimated and the actual row counts.
If you're interested in doing index tuning, may I suggest an article that I wrote for Simple Talk? - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
As for this one. Here are some of the problems that I picked up looking at the exec plan.
Missing an index on tbl_TERM_ID. Consider an index on LINK_TO_TERMINAL, INCLUDE Term_ID, WOD_ID. Consider a second on Term_ID, WOD_ID, INCLUDE LINK_TO_TERMINAL.
Missing an index on tbl_Products. Consider an index on ID
Missing index on tbl_FAILURES_archive_only_first_entrys. Consider an index on Term_ID, WOD_ID and EventDate (in that order).
Missing an index on tbl_f_location_for_pseudo_f_location. Consider one on id_terminal, f_location.
Missing an index on tbl_TERMINAL. Consider one on LINK_TO_PRODUCT_AREA, ID
Missing an index on tbl_PRODUCT_TYPE. Consider one on LINK_TO_PRODUCT_GROUP, ID
Missing an index on tbl_Product_Area. Consider a clustered index (primary key?) on ID.
You have a type mismatch between f_location in tbl_f_location_for_pseudo_f_location and f_location in tbl_FAILURES_archive_only_first_entrys. See if you can fix it so that they are the same data type.
See if you can fix any of that and if it helps at all.
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
April 12, 2009 at 2:09 pm
GilaMonster (4/12/2009)
I suspect the cluster vs heap discrepancy is more the result of a lot of bad and missing indexes.It would really help if you could post the actual execution plan. The one that you gave us is an estimated plan and doesn't have the actual row counts. since bad estimates can result in really bad plans it would be useful to see both the estimated and the actual row counts.
If you're interested in doing index tuning, may I suggest an article that I wrote for Simple Talk? - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
As for this one. Here are some of the problems that I picked up looking at the exec plan.
Missing an index on tbl_TERM_ID. Consider an index on LINK_TO_TERMINAL, INCLUDE Term_ID, WOD_ID. Consider a second on Term_ID, WOD_ID, INCLUDE LINK_TO_TERMINAL.
Missing an index on tbl_Products. Consider an index on ID
Missing index on tbl_FAILURES_archive_only_first_entrys. Consider an index on Term_ID, WOD_ID and EventDate (in that order).
Missing an index on tbl_f_location_for_pseudo_f_location. Consider one on id_terminal, f_location.
Missing an index on tbl_TERMINAL. Consider one on LINK_TO_PRODUCT_AREA, ID
Missing an index on tbl_PRODUCT_TYPE. Consider one on LINK_TO_PRODUCT_GROUP, ID
Missing an index on tbl_Product_Area. Consider a clustered index (primary key?) on ID.
You have a type mismatch between f_location in tbl_f_location_for_pseudo_f_location and f_location in tbl_FAILURES_archive_only_first_entrys. See if you can fix it so that they are the same data type.
See if you can fix any of that and if it helps at all.
Hi,thanks a lot.
I try yours recommend and execution plan is in include files. First is before recommended with name sql_plan_actual, and second is after recommend with name after_gila's_recommend.
Im sorry that I sent first estimate plan, now its will ok, I hope 🙂
and there is statistics its look like better then before:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 24 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 312 ms, elapsed time = 1081 ms.
Table 'tbl_TOP_FAILURES'. Scan count 1, logical reads 1341, physical reads 3, read-ahead reads 629, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TOP_FAILURES_INFO'. Scan count 236, logical reads 2180, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 482, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TERM_ID'. Scan count 6997, logical reads 14008, physical reads 1, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCTS'. Scan count 3, logical reads 33, physical reads 1, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_FAILURES_archive_only_first_entrys'. Scan count 3, logical reads 197, physical reads 4, read-ahead reads 126, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TERMINAL'. Scan count 3, logical reads 15, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_TYPE'. Scan count 145, logical reads 290, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_GROUP'. Scan count 2, logical reads 290, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_PRODUCT_AREA'. Scan count 2, logical reads 290, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_FAILURE_CAUSE'. Scan count 2, logical reads 8, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_f_location_for_pseudo_f_location'. Scan count 1, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_terminal_for_pseudo_f_location'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_product_name_for_pseudo_f_location'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_product_group_for_pseudo_f_location'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(236 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 1669 ms.
SQL Server Execution Times:
CPU time = 1093 ms, elapsed time = 2750 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
BTW: table like product_type, product,product_group,failure_cause etc.. is small max 2000rows and tbl_FAILURES_archive_only_first_entrys is about 4000, I think thats very small to create indexes but statistics is better concrete logical read then maybe not 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply