Francisco Tapia
fhtapia at gmail.com
Wed Jun 10 09:47:51 CDT 2009
excellent, I generally never put a page file on my db drive because I don't ever want any bandwidth stolen from that partition, I also do not place one on my transaction log drive. my experience with ssd has been limited, but I have found that those drives perform extremely fast but only on smaller files (smaller than 3-4gb). if most of your work files are less than 3gb i have found it to be super fast :) almost like loaded in ram fast :). -Francisco http://sqlthis.blogspot.com | Tsql and More... On Wed, Jun 10, 2009 at 5:58 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >