Francisco Tapia
fhtapia at gmail.com
Tue Jun 9 15:24:36 CDT 2009
are you sorting your query John? if you are then moving the tempdb to it's own channel will help speed things up, however if you are not, then the issue may still lie with the page file location. -Francisco http://sqlthis.blogspot.com | Tsql and More... On Tue, Jun 9, 2009 at 12:08 PM, Martin Reid <mwp.reid at qub.ac.uk> wrote: > John > > We have just put the tempdb out onto a Raid 10 configuration to improve > performance. If its an i/o issue that could help. Thats the advice we where > given. > > Martin > > > > Martin WP Reid > Information Services > The Library at Queen's > Tel : 02890976174 > Email : mwp.reid at qub.ac.uk > ________________________________________ > From: dba-sqlserver-bounces at databaseadvisors.com [ > dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia [ > fhtapia at gmail.com] > Sent: 09 June 2009 19:45 > To: Discussion concerning MS SQL Server > Subject: Re: [dba-SQLServer] SQL Server "locks up" > > But the issue that John is describing is that his entire system is locked > up > for up to a minute. I am thinking that the problem lies within the i/o > setup. On my servers here (the massive systems) they are all setup where I > have my mirror drive on one card, and the page file on a 2nd channel on a > different set of drives. > my transaction log is an fiber channel on one volume, as is the database > and > the tempdb. > > I am curious if you could move the pagefile over from the c drive to > another > set of drives(if you have them) to test out this theory. > > > > > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Tue, Jun 9, 2009 at 11:36 AM, David Emerson <newsgrps at dalyn.co.nz> > wrote: > > > John, > > > > One area I have found this to happen is record locking. If I have a > > table open and run a query that accesses the table then SQL seems to > > wait a long time (often until I close the table). > > > > David > > > > At 10/06/2009, you wrote: > > >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. > > > > _______________________________________________ > > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >