March 12, 2009 at 6:29 am
We have a team who does analaysis on our servers where we have TB of data in each databases. This team will query our databases which includes multiple servers at a time doing cross over. most of there queiries include temp tables by which they pull the data they need into temp tables and query out from the temp tables for their final result.
So..for this they open sessions in query window and create all theior stuff and hold them for a week so that they can query those temp tables as aand when they need for that reason they dont close their sessions for not losing temp tables , this made our temp db busy and grow rapidly. sometimes they get transport level error and get disconencted and cry on admin group for disconnecting.
I am searching for a best way to handle this situation.
options:
1. Instead of creating temp tables how about creating real tables and do their work and when they are done they can drop.
2. each person has a database in each server where they have (R/W/ddl_admin) previlages.
Guys... please input your valuable suggestions in this scenario to handle THE BEST for performance.
Thanks
March 12, 2009 at 6:45 am
Not sure I'm a big fan of them using "real" tables in your database, as they make inadvertently drop a table that you don't want being dropped. However I think it would be fair to give them their own space, whether it be their own db, or another a shared database that they may do whatever they need to.
I might think about hosting this on a separate instance for security reasons, or perhaps on a separate machine altogether so that if they kick off a runaway query they don't affect the performance of your production datasources.
I'd do minimal backups for this database and probably have it in Simple recovery, perhaps a full on Sat with a diff every night, something without much overhead for you, but would still allow them to get to a point where they couldn't complain too much if they hosed something up 'cause they would at least get something back...
Each person having a db on each server seems a bit like a headache to maintain, make them use schemas and they should be able to share nicely...
-Luke.
March 12, 2009 at 7:06 am
All depends on how much time you have to spend on it I suppose 🙂
I would feel out the situation and see if creating regular data copies to tables elsewhere (an ETL job, etc) that users can report on would work and then see what they report on in Ad hoc and try and setup better indexes perhaps .. educate them on the best way to query the data. Giving them access to their own database with bulk insert type access could be dangerous as they will most likely have a bunch of table heaps that take up (probably unmonitored) space that run inefficiently.
Try not to lose control over your sql server...
March 12, 2009 at 7:57 am
You can also spread Tempdb across multiple physical devices, in some ways treat it like a real busy database and plan it out as such.
Also are you running snapshot isolation?
March 12, 2009 at 9:45 am
snapshot isolation ????
March 12, 2009 at 10:03 am
For more on snapshot isolation
http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx
So my guess is you are not using it. Snapshot isolation can also have a tempdb impact, that is why I asked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply