July 11, 2011 at 2:37 am
Ninja's_RGR'us (7/8/2011)
pwalter83 (7/8/2011)
Ninja's_RGR'us (7/7/2011)
The missing clustered index is the best path for improvement at this point.The 7 nc index would only slow down the import of the data (assuming it's a massive import).
I know to use the date column for the clustered index based on 1000s of tests.
Thanks Ninja...there is always a massive amount of data in this table at any given point of time. Would you suggest dropping some of the NC indexes ? this table is used for other reports as well and I hope those dont get affected with the changes.
By the way what criteria should be used to decide which NC indexes to remove ?
As I said those index will only affect the INSERTS of data (and deletes / updates depending on how you clean that table).
Reporting tables are best served by having tones of indexing on it.
Really the ONLY thing I'd touch at this point is the clustered index on date. Now the only way to be sure is to run the all reports (as much as you an squeeze in a short test). Log the results in profiler. Then add the clustered index and rerun the exact same reports. Compare to see which one ran the fastest and you'll see what is best for your solution.
I'd also make sure to rebuild all the indexes after any big data import / purge. Make sure fill factor is at 0 (same as 100%). Once the rebuild is done update any stats that were not included in the indexed columns.
You can also log the actual execution plan and stats in profiler. That event is in the performance section tho I'm not 100% sure which one it is from memory. Doing so allows you to compare if you really messed things up. The easiest counter is cpu time and reads. Then in the plan is the estimated cost of the final operator.
B4 doing any test have a run at rebuilding the stats / indexes.
Here's a good article on tuning as well to get you started :
The thing is the report displays data based on either Sailing date or Onboard date as per the user selection. Which of these dates should the clustered index be created on ? or should it be on both ?
Appreciate your help.
July 11, 2011 at 5:10 am
Can't be both. This is where intimate knowledge of how the data is used is critical.
Do you have access to the reportserver database and are you logging the reports' executions? If so we can start digging in there to see which one is the best.
If you don't have access to that then make your best guess by asking the users which ones they use the most.
July 12, 2011 at 8:34 am
Ninja's_RGR'us (7/11/2011)
Can't be both. This is where intimate knowledge of how the data is used is critical.Do you have access to the reportserver database and are you logging the reports' executions? If so we can start digging in there to see which one is the best.
If you don't have access to that then make your best guess by asking the users which ones they use the most.
I have found the date on which the clustered index can be created on, however, the problem now I face is that the date column is null. As far as I understand, its not possible to create a clustered index on null columns. Do you if this can be achieved ? I have tried googling this but could not get any definitive answers.
July 12, 2011 at 8:40 am
CREATE TABLE #test
( id INT NULL)
CREATE CLUSTERED INDEX #CIX ON #test (id)
INSERT INTO #test (id) VALUES (null)
INSERT INTO #test (id) VALUES (1)
INSERT INTO #test (id) VALUES (2)
INSERT INTO #test (id) VALUES (null) --fails only if the column is unique
SELECT * FROM #test
--NULL
--NULL
--1
--2
DROP TABLE #test
July 13, 2011 at 5:24 am
Ninja's_RGR'us (7/12/2011)
CREATE TABLE #test( id INT NULL)
CREATE CLUSTERED INDEX #CIX ON #test (id)
INSERT INTO #test (id) VALUES (null)
INSERT INTO #test (id) VALUES (1)
INSERT INTO #test (id) VALUES (2)
INSERT INTO #test (id) VALUES (null) --fails only if the column is unique
SELECT * FROM #test
--NULL
--NULL
--1
--2
DROP TABLE #test
Thanks Ninja,
actually after creating a clustered index on the table, the report started timing out with out of memory exception error after a few seconds. After I removed the clustered index, it started processing (though slow) but displayed data this time. I think the clustered index doesnt work on this one and dont know what my other options are at this point of time.
July 13, 2011 at 5:27 am
What gave the error? Bids or SQL SERVER?
Please post the actual execution plan of the query with the clustered index. I'll have a look if I can improve it.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply