jwcolby
jwcolby at colbyconsulting.com
Wed Jun 10 07:58:48 CDT 2009
Francisco (and everyone trying to help), Thanks for pointing me back to the pagefile. I had just rebuilt this server and the first thing I did was go in and set the page file size... to... 1200 mbytes. Uhh.... ooops. That should be 12 gbytes (12000 mbytes). I had inadvertently left off a zero. This will very likely explain the whole problem. I upped that page file to 12000 mbytes and created a second page file on the same volume as the database is on. If I shouldn't do that (same volume as the db) let me know but I have read somewhere to create multiple page files and let Windows take it's pick. BTW, I have a very fast 32 gig SSD which I am using only 8 gigs of (working disk for virtual machines). I have read about issues using SSDs but have no direct experience using them for writing to - my VMs only read from the drive. So I could even place a pagefile out on the ssd. BTW, I just did the same update hash fields operation on 50 million records in 21 minutes that it took 3.5 HOURS to do yesterday on about 28 million records. The page file size error was definitely crippling the system. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > 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 > >