SQL Server Performance Forum – Threads Archive
T-SQL performance issueHi All I have situation where I have to run a query on production server which process 10 million records (probably more). It is simple "select *" query. If I run this query, server performance will be degrade dramatically and other user also need to access this table which would be definitely problem to these users while running this query. Question is: what would be ideal approach to run this query on production server (I have to run on production server and day time). what option I have to run this query? Thanks for help in advance
Hi, What exactly is it that you’re doing with this query? Are you returning 10 million records? Because if you are I’d have to question what anyone would want to do with 10 million records. A little more background on the query might help. I’m assuming that it only returns data (it doesn’t edit data) – which sounds like it could have some reporting functionality. If this is the case then you might want to consider using a seperate reporting database – but you’d then need to think about transferring data to the reporting database somehow. Put simply, a highly transactional design is not going to lend itself to reporting on data (and vice-versa). So running reports on the database is going to take long (because the design wasn’t designed for reporting) and it’s going to adversely affect the transactional side of things. If using a seperate reporting database is not an option, if you’re not bothered about dirty reads then it might be worth considering using the NOLOCK hint (or read uncommitted isolation level). Karl Grambow www.sqldbcontrol.com
Hi Karl Thanks a lot for such explanation… it is not for reporting purpose, so your suggestion at the end is more appropriate. I was just curious about the situation that if I have this kind of sitution then what would be the solution or way to reduce the burden on server while running this kind of query on production server.
could please ellaborate little more about "NOLOCK Hint" and "Read Committed" that how they help in this typical situation. Thanks again
Probably this will give you an impression of NOLOCK and READ COMMITTED:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12779 —
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs
Also check what kind of scheduled jobs are running at the same time when this queryis executing, make sure there are no DBCC statements are running during the same time. As they tend degrade the performance. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.
Hi Guys thanks a lot .. It is very useful to understand this topics… regards to all of you…