Solved

Need SQL Server Performance Help

Posted on 2012-03-13
36
313 Views
Last Modified: 2012-06-27
I am running SQL Server 2008 and have a question about performance.

I have a sproc that does a simple select on 8 joined tables.The select has some WHERE conditions; many involve “CASE” statements.

All the tables have relationship set up, all the non-PK/FKfields that are used in the conditions have indices set up.

When I execute this proc with most values, it returns thedata set in a second or faster.

With one particular set of values, it takes 4.5-5 seconds toreturn the data set and I cannot figure out why.

I removed conditions and added them back one at a time untilthe problem happened and I found where it was happening. The code listed from lines 71-76 is the offending condition:

ALTER PROCEDURE [dbo].[usp_get_fixtures]
      @SYSTEM_ID INT,
      @FINISH_ID INT= 7,
      @INPUT_ID INT= 3,
      @OUTPUT_ID INT= 1,
      @MOUNT_ID INT= NULL,
      @SUB_SYSTEM2_ID INT= 0,
      @IS_CONFIG BIT= 1
AS

BEGIN

      SET NOCOUNT ON;

      SELECT DISTINCT 
            p.NAME,
            xpis.IS_NEW,
            xpis.IS_LED,
            xpis.IS_CF,
            xpis.IS_ADA,
            xpis.IS_ENERGY_STAR,
            xpis.IS_RAILTONES,
            xpis.PRODUCT_NAME_ID, 
            xfpc.FINISH_CHILD_ID,
            p.SUB_SYSTEM2_ID,
            p.ACCESSORY_REQUIRED,
            p.CATALOG_DESCRIPTIONAS PRODUCT_DESCRIPTION,
            p.CATALOG_DESCRIPTION2AS PRODUCT_DESCRIPTION2,
            p.ELG_SPECSHEETAS SPECSHEET,
            c.COLOR_IDAS DEFAULT_COLOR_ID,
            c.NAMEAS DEFAULT_COLOR
      FROM 
            dbo.xref_SKU_SYSTEMAS xss INNER JOIN
            dbo.tbl_SKUAS sku INNER JOIN
            dbo.tbl_PRODUCT_NAME AS p ON sku.PRODUCT_NAME_ID = p.PRODUCT_NAME_ID ON xss.SKU_ID =sku.SKU_ID LEFT OUTER JOIN
            dbo.tbl_COLORAS c ON p.DEFAULT_COLOR_ID = c.COLOR_ID LEFT OUTER JOIN
            dbo.xref_FINISHES_PARENT_CHILDAS xfpc LEFT OUTER JOIN
            dbo.tbl_FINISHAS f ON xfpc.FINISH_PARENT_ID = f.FINISH_ID ON sku.FINISH_ID = xfpc.FINISH_CHILD_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_ICONS_SYSTEMSAS xpis ON p.PRODUCT_NAME_ID =xpis.PRODUCT_NAME_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_MOUNT AS xpm ON p.PRODUCT_NAME_ID =xpm.PRODUCT_NAME_ID

      WHERE 
            (p.SUB_SYSTEM_ID = 9) AND
            (p.IS_OBSOLETE = 0) AND
            (sku.IS_OBSOLETE = 0) AND
            (p.NAME NOT LIKE '%dummy%') AND
            (xss.SYSTEM_ID = @SYSTEM_ID OR xpis.SYSTEM_ID= @SYSTEM_ID) AND
            (
                  (
                        CASE @IS_CONFIG
                              WHEN 0 THEN 0
                              ELSE sku.IS_OPTION
                        END
                  ) = @IS_CONFIG
            ) AND
            (
                  (
                        CASE @SUB_SYSTEM2_ID
                              WHEN 0 THEN 0
                              ELSE p.SUB_SYSTEM2_ID
                        END
                  ) = @SUB_SYSTEM2_ID
            ) AND
            (
                  (
                        CASE @FINISH_ID
                              WHEN 0 THEN 0
                              ELSE sku.FINISH_ID
                        END
                  ) = @FINISH_ID OR
                  (
                        CASE @FINISH_ID
                              WHEN 0 THEN 0
                              ELSE xfpc.FINISH_PARENT_ID
                        END
                  ) = @FINISH_ID
            ) AND
            (
                  (
                        CASE @OUTPUT_ID
                              WHEN 0 THEN 0
                              ELSE sku.OUTPUT_VOLTAGE_ID
                        END
                  ) = @OUTPUT_ID OR sku.OUTPUT_VOLTAGE_ID ISNULL
            ) AND
            (
                  (
                        CASE @INPUT_ID
                              WHEN 0 THEN 0
                              ELSE sku.INPUT_VOLTAGE_ID
                        END
                  ) = @INPUT_ID OR sku.INPUT_VOLTAGE_ID ISNULL

            ) AND
            (
                  (
                        CASE @MOUNT_ID
                              WHEN 0 THEN 0
                              ELSE xpm.MOUNT_ID
                        END
                  ) = @MOUNT_ID OR xpm.MOUNT_ID ISNULL
            )

Open in new window


Note that if I remove the code in red, it runs quickly. Alsonote that even if I remove the code in lines 71-76, and I add the code on line 24, it takes 5-6 seconds to return the data set. So it seems there is something odd with that particular table.

Again, this is only for certain values executed in the stored procedures.

Again, the “xref_FINISHES_PARENT_CHILD” table has relationships and keys set up and is a  simple table with a PK and two FKs. But both FKs point to the same column in the same table.

Here’s the xref_FINISHES_PARENT_CHILD table:

CREATE TABLE [dbo].[xref_FINISHES_PARENT_CHILD](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [FINISH_PARENT_ID] [int] NULL,
      [FINISH_CHILD_ID] [int] NULL,
 CONSTRAINT[PK_xref_FINISHES_PARENT_CHILD] PRIMARY KEY CLUSTERED 
(
      [ID] 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

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The primary key.' ,@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The foreign key to the finish table. This is the parentfinish.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'FINISH_PARENT_ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The foreign key to the finish table. This is the childfinish to the parent.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'FINISH_CHILD_ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A cross reference table that relates finishes to a parentfinish.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD'

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]  WITH CHECK ADD CONSTRAINT [FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH]FOREIGN KEY([FINISH_PARENT_ID])

REFERENCES [dbo].[tbl_FINISH] ([FINISH_ID])

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]CHECK CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH]

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]  WITH CHECK ADD  CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH1] FOREIGNKEY([FINISH_CHILD_ID])

REFERENCES [dbo].[tbl_FINISH] ([FINISH_ID])

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]CHECK CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH1]

GO

Open in new window


Any help would be awesome.

Thanks!
0
Comment
Question by:infotechelg
  • 14
  • 13
  • 8
  • +1
36 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37716337
I would try re-coding to (hopefully) allow short-circuiting to be done.
Instead of:
                  (
                        CASE @IS_CONFIG
                              WHEN 0 THEN 0
                              ELSE sku.IS_OPTION
                        END
                  ) = @IS_CONFIG
            )

Try:

                  (
                        @IS_CONFIG = 0 OR @IS_CONFIG = sku.IS_OPTION
                  )

For every such param check.



Then try recompiling the proc every time so that parameter snipping can be used accurately:

ALTER PROC ...
WITH RECOMPILE
AS
...

Typically recomplile time is much less than the time saved during execution.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 37716341
How often do you reindex/update stats on your DB?

Did you checked the execution plans to see if any index(es) are needed - from Query menu select "Include Actual Execution Plan" before running it and maybe do a UPDATE STATISTICS on the table where you suspect the issue is.
0
 

Author Comment

by:infotechelg
ID: 37716523
lcohan, good point. I'll try reindexing and see if that does it. Then I'll try the other things you suggested.

Scott, thanks for your suggestion. I don't think it's that just because if I remove that one piece of code, it's fine. But, if the indexing thing doesn't work, i'll give it a shot. can't hurt, right?
0
 

Author Comment

by:infotechelg
ID: 37716590
So, I reindexed all indices in the database using the code pasted below and the problem still exists.

DECLARE @TableName VARCHAR(255)
 DECLARE @sql NVARCHAR(500)
 DECLARE @fillfactor INT
 SET @fillfactor = 80
 DECLARE TableCursor CURSOR FOR
 SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
 FROM sys.tables
 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
 EXEC (@sql)
 FETCH NEXT FROM TableCursor INTO @TableName
 END
 CLOSE TableCursor
 DEALLOCATE TableCursor

Open in new window


Running EXEC sp_updatestats didn't work either.

Regarding the "Execution Plan" I did that but, to be honest, I'm not quite sure what I am looking for.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716591
Absolutely no issue to run that but make sure you use REBUILD with ONLINE = ON on live DB's.

Also, no matter fresh index/stats the issue may still exists for specific data sets and that you could see only from the query plan.

I usually UPDATE STATISTICS on ALL tables involved in a SP and then sp_recompile that SP.
0
 

Author Comment

by:infotechelg
ID: 37716639
I noticed it suggested I add an index to a different table. I did, but it didn't change anything.
0
 

Author Comment

by:infotechelg
ID: 37716688
Execution Plan on ProcOk, so I did the UPDATE STATISTICS, then I added "WITH RECOMPILE" to my proc and executed that.

Then I EXEC the proc again and got the same results.

I took a screenshot of the execution plan where the cost was high, but I can't decipher this.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716702
Sorry - I can't see any code in red and could you please just paste that piece giving the issue if its in the stored proc?

I suspect some (huge) cartesian due to OUTER JOINS where you may need to add another condition to further filter the data but not sure where without being able to see the actual plan. Can you see any thik lines in the execution plan? If you hoover over it should give you a picture of how many rows are produced on the backend record set to support your query
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716713
The first hash match by percentage then second are your 100% issue and that is due to the OUTER JOINs
0
 

Author Comment

by:infotechelg
ID: 37716726
Scott, I tried @IS_CONFIG = 0 OR @IS_CONFIG = sku.IS_OPTION, but that didn't work. Thanks for the suggestion.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37716743
>> @IS_CONFIG = 0 OR @IS_CONFIG = sku.IS_OPTION, but that didn't work <<

?  What does "didn't work" mean?

It should do the same thing as the other code, except it avoids the CASE and might allow SQL to use parameter sniffing to treat the expression as a constant rather than re-evaluate it for every row.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716758
Can you add a parrallel index on dbo.tbl_SKUAS like IX_SKU_OBSO but having INCLUDE all columns you use from it in this query? that would be to get rid of the first hash join and clustere index scan xref_SKU_SYSTEM. Similar method can apply for the next hash join.
Just add the index with ONLINE = ON to not lock the table if its online.
0
 

Author Comment

by:infotechelg
ID: 37716761
lcohan, the code in "red" should have been lines 71-76. My apologies.

I tried getting rid of the outer joins, but that didn't help. Actually made it worse.
0
 

Author Comment

by:infotechelg
ID: 37716765
Sorry Scott, I meant it didn't fix the problem.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716806
so did you tried adding an index like:

create index IX_SKU_OBSO_COVER on tbl_sku
(same_columns_like_IX_SKU_OBSO_here)
INCLUDE
(PRODUCT_NAME_ID,SKU_ID,FINISH_ID,IS_OPTION,OUTPUT_VOLTAGE_ID,INPUT_VOLTAGE_ID)
with (ONLINE = ON, FILLFACTOR = 80)


This should help to get rid of the hash join and the clustered index scan because all those fields would be now INCLUDed in the new index
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37716807
What specifically "doesn't work" about this::



ALTER PROCEDURE [dbo].[usp_get_fixtures]
      @SYSTEM_ID INT,
      @FINISH_ID INT= 7,
      @INPUT_ID INT= 3,
      @OUTPUT_ID INT= 1,
      @MOUNT_ID INT= NULL,
      @SUB_SYSTEM2_ID INT= 0,
      @IS_CONFIG BIT= 1
WITH RECOMPILE
AS

BEGIN

      SET NOCOUNT ON;

      SELECT DISTINCT
            p.NAME,
            xpis.IS_NEW,
            xpis.IS_LED,
            xpis.IS_CF,
            xpis.IS_ADA,
            xpis.IS_ENERGY_STAR,
            xpis.IS_RAILTONES,
            xpis.PRODUCT_NAME_ID,
            xfpc.FINISH_CHILD_ID,
            p.SUB_SYSTEM2_ID,
            p.ACCESSORY_REQUIRED,
            p.CATALOG_DESCRIPTIONAS PRODUCT_DESCRIPTION,
            p.CATALOG_DESCRIPTION2AS PRODUCT_DESCRIPTION2,
            p.ELG_SPECSHEETAS SPECSHEET,
            c.COLOR_IDAS DEFAULT_COLOR_ID,
            c.NAMEAS DEFAULT_COLOR
      FROM
            dbo.xref_SKU_SYSTEMAS xss INNER JOIN
            dbo.tbl_SKUAS sku INNER JOIN
            dbo.tbl_PRODUCT_NAME AS p ON sku.PRODUCT_NAME_ID = p.PRODUCT_NAME_ID ON xss.SKU_ID =sku.SKU_ID LEFT OUTER JOIN
            dbo.tbl_COLORAS c ON p.DEFAULT_COLOR_ID = c.COLOR_ID LEFT OUTER JOIN
            dbo.xref_FINISHES_PARENT_CHILDAS xfpc LEFT OUTER JOIN
            dbo.tbl_FINISHAS f ON xfpc.FINISH_PARENT_ID = f.FINISH_ID ON sku.FINISH_ID = xfpc.FINISH_CHILD_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_ICONS_SYSTEMSAS xpis ON p.PRODUCT_NAME_ID =xpis.PRODUCT_NAME_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_MOUNT AS xpm ON p.PRODUCT_NAME_ID =xpm.PRODUCT_NAME_ID

      WHERE
            (p.SUB_SYSTEM_ID = 9) AND
            (p.IS_OBSOLETE = 0) AND
            (sku.IS_OBSOLETE = 0) AND
            (p.NAME NOT LIKE '%dummy%') AND
            (xss.SYSTEM_ID = @SYSTEM_ID OR xpis.SYSTEM_ID= @SYSTEM_ID) AND
            (@IS_CONFIG = 0 OR @IS_CONFIG = sku.IS_OPTION) AND
            (@SUB_SYSTEM2_ID = 0 OR @SUB_SYSTEM2_ID = p.SUB_SYSTEM2_ID) AND
            (@FINISH_ID = 0 OR @FINISH_ID = sku.FINISH_ID) AND
            (@FINISH_ID = 0 OR @FINISH_ID = xfpc.FINISH_PARENT_ID) AND
            (@OUTPUT_ID = 0 OR @OUTPUT_ID = sku.OUTPUT_VOLTAGE_ID OR sku.OUTPUT_VOLTAGE_ID IS NULL) AND
            (@INPUT_ID = 0 OR @INPUT_ID = sku.INPUT_VOLTAGE_ID OR sku.INPUT_VOLTAGE_ID IS NULL) AND
            (@MOUNT_ID = 0 OR @MOUNT_ID = xpm.MOUNT_ID OR xpm.MOUNT_ID IS NULL)
           
END
0
 

Author Comment

by:infotechelg
ID: 37716820
Can you add a parrallel index on dbo.tbl_SKUAS like IX_SKU_OBSO but having INCLUDE all columns you use from it in this query? that would be to get rid of the first hash join and clustere index scan xref_SKU_SYSTEM.

lcohan, i'm sorry, but I'm not following what you're asking me to do.
0
 

Author Comment

by:infotechelg
ID: 37716829
Scott, I said I meant to say it didn't fix my problem. The query still exceutes slowly.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:infotechelg
ID: 37716889
lcohan, I added this (not sure if that was what I was supposed to do) but it still didn't make the query execute faster.

create index IX_SKU_OBSO_COVER on tbl_sku (IS_OBSOLETE)
INCLUDE (PRODUCT_NAME_ID,SKU_ID,FINISH_ID,IS_OPTION,OUTPUT_VOLTAGE_ID,INPUT_VOLTAGE_ID)
with (ONLINE = ON, FILLFACTOR = 80)

Open in new window


Again, this only happens when I pass certain parameters into the proc. Most parameters I pass return the results set in a second or less.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37716907
I understand that and is that when you captured the execution plan?
How does the plan changed with the new index? The first and most expensive hash join (actualy the last on the right end of the picture) should go away.
You need to do a UPDATE STATISTICS tbl_sku and sp_recompile [dbo].[usp_get_fixtures] before you can see a change in plan but causion with sp_recompile in prod environment
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37716912
It's possible that those specific values hit a "tipping" point, causing SQL to either:

(1) not use an index it previously used;  OR
(2) use an index it previously did not use.

And that is causing it to take additional time to do the query.

If so, outside of query hints, you won't be able to change that.
0
 

Author Comment

by:infotechelg
ID: 37716933
Update Execution Planlcohan, ok, I ran the UPDATE STATISTICS and recompiled the proc. Attched is the resulting execution plan.
0
 

Author Comment

by:infotechelg
ID: 37716939
Scott, yeah, I'm not sure. Do you think I should try deleting the offending table and then recreate it?
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37716948
You could use following to help you debugging the query issue IO (physical reads are worst) related:

set statistics time on;
set statistics io on;


also a

set showplan_text on;

should help you debug your query - look for nested loops in it or anything costly in the worst case scenario parameters value
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37716949
Nah, no need for that.

If you have a table that's (badly) fragmented, you should probably reorg or rebuild that table.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37717033
Easyest way to "fix" a fragmented table is to eliminate the root cause - recreate the clustered index on it and that will do it. DROP/CREATE with ONLINE = ON should work for you if you know that table is soo badly fragmented.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37717060
Also in addition to all of the above from my experience sometimes the data cardinality changes soo much that existing queries and table indexes are no longer sufficient as they are - similar to what you have - just because SQL has to process soo much on the back end with same hardware(RAM) so you may have lots of IO instead of mem utilisation (cached data) for your queries.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37717122
and just somewhat side note - you know that <<(p.NAME NOT LIKE '%dummy%')>> will invalidate any index usage on that column even if exists because of the % at both ends of the string. Also you could rewrite just that portion like


(NOT p.NAME LIKE '%dummy%')


and also...I personaly found (perfromance) issues with SQL queries where round brakets ()  are used where not necessary.

I mean you need them in

.....
(xss.SYSTEM_ID = @SYSTEM_ID OR xpis.SYSTEM_ID= @SYSTEM_ID) AND .....

but not necessary in each of those above in the WHERE clause in my opinion.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 37717127
Hold on.

DO NOT willy-nilly drop and recreate a clustered index.

If you need to defragment a clus index, use ALTER INDEX ... REBUILD (or REORGANIZE).

If you are on Enterprise edition, you can use also use WITH ( ONLINE = ON ).

Likewise for non-clustered indexes.

You can use the query plan to see the indexes used by the query.

Then check those indexes for fragmentation; if any are (badly) fragmented, rebuild/reorganize them before continuing.

Defragmenting indexes is a needed first step before doing other tuning for a query.  The basics have to be in place before you move on to other things.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37717183
Not to contradict ScottPletcher however from my experience I found that ALTER INDEX ...REORGANIZE does nothing or not much and I thought the indexes were rebuilt if you look above.

Obviously on a production box you won't drop/create a clustered index just like that in the middle of the day and I think I mentioned that but...again I think is the easiest  way to get rid of fragmentation (and only way to get rid of the unused space) from that table.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 37717231
Dropping a clus index will force a rebuild of all non-clus indexes.

Then when you add the clus index back all non-clus indexes will have to be rebuilt AGAIN.

Thus, on NO box do you drop a clus index unless it's absolutely necessary.  And if you intend to re-add a clus index, you drop all non-clus indexes first.

A REBUILD *WILL* get rid of fragmentation *AND* unused space.  

REORGANIZE will do less, but it is much less overhead.  Reorg is better than nothing, but not nearly the advantages of a full rebuild, of course.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37717247
You could also look at matching the data type from ALL your input parameters to be exactly the same as the table column you go against and even though your query plan does not show it try to use a singel processor for the query by using MAXDOP=1 option.

I think the link below is recent enough and could provide quick and clear details about what you see in the execution plan.

http://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37717311
I do not want to get into any fights here - not my purpose - however the statement below:

"A REBUILD *WILL* get rid of fragmentation *AND* unused space. " is true only in theory...

Please try it on your own on a TEST not PROD box:

Look at a table that has lots of unsued space and a clustered index on it (implicit fragmenation) from "Disk Usage by Top tables" SQL standard report in SSMS or any DM view.

Now try everything possible less CLUSTER index drop and check again - space unused will be still there (any way you look at it) until you re-create the clustered index.


Good Luck - I'm off...
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 37717371
I guess you're not aware of the deferred processes SQL uses when dropping/rebuilding any moderately big index.

When indexes with 128 extents or more are dropped or rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.  They are then done in (small) "batches".  Thus, the total space may not be technically freed for some time after the rebuild.  But it HAS been deallocated from that table.

The disk space will still be reserved for that db file, of course, as all such space is.  SQL never frees disk back to the disk system until you explicitly tell it to by shrinking the file (which itself causes a lot of fragmentation -- D'OH!).
0
 
LVL 39

Expert Comment

by:appari
ID: 37718242
Just a thought, can you check what happens if you replace all the parameter values in the sql and execute the select sql instead of the stored procedure.
Check how much time it takes?
0
 

Author Comment

by:infotechelg
ID: 37725724
I want to thank everyone for their help. While the particular issue wasn't solved by these suggetions, you provided a lot of great DBA-type tips that i'll most definitely use from here on out.

Thanks again.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Server 2008 Cluster Fail-over Errors 5 53
using t-sql EXISTS 8 23
SQL Server Error Log - logging period 1 21
Update in Sql 7 12
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now