Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2008-12-14 : 07:54:38
|
| I started on a performance reporting project on an ITIL Support System, which was going fine to start with, but the database owners started complaining about everything slowing down and blamed the crashes on me.I tried lots of things to decrease the amount of rows queried, but I needed all the history to make a true report. I never UPDATED anything - just using a view to SELECT stuff at the same time as IT Support were creating new rows on Production. I was told there was no resource to have a secondary server to report from.Are there special kinds of locks you use on a view / query to prevent what data you're looking at being UPDATED or new rows bing inserted to the table during the period of your query to distort your results? Is there a fix to what I was doing? I know to run a trace in Profiler might have helped at the time but I didn't have privs for that - a developer, not DBA for this project - the kind of company who doesn't have a DBA at all!!I was pulling 170,000 rows over about a minute for every refresh of the report, set to go live and run frequently, when IT Support would just bring up 1 record to look at, UPDATE or add a new one at a time, so here is the imbalance! How do you do this?170,000 was for 5 years history, so running it for the last month was ~2,833 rows but still too many at once and not a very good representation of 5 years. There must be a really straightforward way to get all those rows every minute without disturbing crucial data input and causing locks... |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-14 : 10:46:59
|
| You should use NOLOCK to discard reader and writer contention issue if your data being fetched doesn't have to so current. |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-15 : 06:13:07
|
| If you are using SQL Server reporting services for reporting your reports may need tweeked to take advantage of caching. E.g. are you using filtering to allow a larger cache to be storedLook at the indexing to improve the speed of you queries, also if possible partition table to seperate off historical data from current data.Moving tempdb onto its own disk should help if you do not already do it. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-12-15 : 10:53:36
|
| Brilliant replies. What is NOLOCK and how do I implement it? That's probably the whole answer. Fetching rows can certainly take second place to anyone updating or inserting rows, but for parts of the report there needs to be accuracy over the last few seconds, as we're looking for a count of 'calls per hour' which would be skewed if an update / insert put a read on pause...Otherwise, indexes have been used far too much I think. There are nonclustered on so many of the columns on so many of the underlying tables and I've read that too much indexing can really slow things down.I don't have privs to do much apart from write the reports and queries and views, but it'll be down to speaking to the bosses to get some rights. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-16 : 05:01:55
|
| Select * from table with (nolock) - look up locking hints for other variationsExtra indexes will slow down writes (updates, inserts and deletes) because the indexes will need maintained. Extra indexes will not slow reads - they will either improve read or not affect it. Indexed Views may be useful for your reports. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-16 : 05:05:54
|
170 000 records should not take a minute to return.Unless there are missing indexes.Mike, check your execution plan and post a link to the executionplan (*.sqlplan) here.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Server Crashes