[dba-SQLServer] SQL Server "locks up"

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.