[dba-SQLServer] SQL Server "locks up"

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