jwcolby
jwcolby at colbyconsulting.com
Tue Jun 9 13:26:11 CDT 2009
All of them are in the same raid card. The OS is on a raid 1 pair. The page file is on the C: drive. The database is all on a single raid 6, even the same volume. That is what the questions I was asking were aimed at but I didn't really get much response on how to spread things around so I just did what I know. I have a set of 6 terabyte drives, which taken together gives me two 2 tbyte volumes, raid 6. I find it difficult to imagine that the disk stuff would cause the server to lock up tight when... I am running a query (yes it is large) and I click on another database to just look at the views contained in that database, or open a stored procedure to see what it looks like. It just seems like that should be instantaneous. I have told SQL Server to leave 3 gigs of ram available (only take 5 of the 8 gigs). It appears that SQL Server does indeed respect that because the size of the committed memory is only 5 gigs, and increases if I go in and increase that number while the query is running. So in theory I have 3 gigs available for the OS and other apps. The processors are idling. And yet it does not matter what I do, from look at parts of the databases to trying to open Excel or Access, it just takes 60 seconds or more to respond. I have no idea how to troubleshoot this, but the one constant is that I do not have this issue unless SQL Server is busy. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > Are these large queries? What's your disk distribution setup like? > Where is the OS disk what channel on your raid card, Where is your Page File > on what channel / Raid Card, where is your DB, what chan... well you get the > picture... > > If it's all processing via the same raid card I'm afraid you may have to > troubleshoot that part. There is absolutely no reason your server should > hang while running a view, unless there are problems with the view, or the > design on your I/O layout. > > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Tue, Jun 9, 2009 at 4:56 AM, jwcolby <jwcolby at colbyconsulting.com> wrote: > >> Why does SQL Server "lock up" when I am running a query and I try to do >> something such as view the >> design of a view on a completely different view? >> >> BTW SQL Server has been limited to 5 gigs out of 8 gigs total memory. >> >> It seems that SQL Server should be able to do things like this with >> absolutely no hesitation. Yet >> when I am running a large query and I try to do anything else, such as view >> the tables or views, >> view the SQL in a stored procedure etc., even in a completely different >> database, the entire system >> just locks up for a long time, as in a minute. The odd part is that the >> query isn't even using much >> cpu. I have a quad core and the total cpu usage in task manager says 0%, >> yet I can't get anything >> else to work. Even something like saving a change to an Access module and >> closing Access takes a >> full minute. >> >> It is truly annoying. I often have to do other things on the machine while >> queries run and it is >> just impossible to get anything else done. >> >> -- >> John W. Colby >> www.ColbyConsulting.com >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >