jwcolby
jwcolby at colbyconsulting.com
Thu Nov 4 08:50:13 CDT 2010
LOL, of course not. ;) It did appear to notice the memory changes, i.e. I could watch the memory used rise and fall out in task manager as SQL Server grabbed and released the memory. I no longer use stored procedures btw, all of this stuff was directly executing a view - select * from vCntXyZ in a query window. John W. Colby www.ColbyConsulting.com On 11/4/2010 9:39 AM, Francisco Tapia wrote: > John, > Just curious but did you shut down the instance after each change or clear > the buffers and cache to ensure proper benchmarking? > > You can clear the cache by running DBCC DROPCLEANBUFFERS, Then run DBCC > FREEPROCCACHE, which clears the stored procedure cache. > > -Francisco > http://bit.ly/sqlthis | Tsql and More... > > > > > On Wed, Nov 3, 2010 at 6:22 PM, jwcolby<jwcolby at colbyconsulting.com> wrote: > >> I am processing an order for the client that i built this server for. One >> of the steps is to count >> the available records. >> >> The task, select 640K records from two related tables, 65 million names / >> addresses joined to 50 >> million records of demographics (the database from hell). Filter down to >> (male, high income, young) >> and (has kids or mail order buyers). The selection views return full name >> / address plus selection >> fields. >> >> Both tables have a clustered index on integer PK. The tables are joined on >> PK/FK (the key of the >> clustered index). Cover indexes on the selection fields. The tables are >> in separate databases, >> both databases on the same SSD (2 drive raid 0). The count simply counts >> the PKID of the selection >> view. >> >> The server was configured with 6 of 8 cores available to SQL Server and 24 >> gigs of RAM. The count >> ran consistently around 0:1:55 (one minute 55 secs). The system was only >> running the 6 cores around >> 20% -25% of capacity. >> >> I cut the processors assigned to 3 of 8 and reran the same count. The >> three assigned processors ran >> about 80% of capacity, but several of the processors not assigned to SQL >> Server also ran something, >> averaging about 30-40% of capacity. The time to do the count was about >> 3:36. >> >> I then cut the memory assigned to SQL Server to 12 gigs with 3 cores >> assigned. Again, the >> processors ran very similar to the last run, the three assigned to SQL >> Server ran around 60-80% but >> a couple of the other cores not assigned also did something significant - >> 30-40%. The time to do >> the count was 2:06. >> >> I then assigned 6 processors but 12 gigs of memory. The 6 processors >> assigned averaged around 80% >> for much of the time, but the total time was 1:53. >> >> I then jacked the memory back up to 24 gigs / 6 processors. Average core >> utilization dropped, the >> total time was 2:01. >> >> Just to see if it was an anomaly I dropped back down to 3 procs with 24 >> gigs of memory. 4:00 to >> process the count. >> >> And finally back to 12 gigs and 3 procs. 2:00 >> >> So 12 gigs and 3 cores produced equivalent results to 6 cores and 24 gigs >> (which I find fascinating >> and disturbing). 3 cores and 24 gigs put on a very poor show. >> >> And of course this test did not have the server doing anything else. >> >> I had intended to run a VM on the server though I am changing my mind. In >> preliminary tests, the vm >> did not perform as well as on the previous server. I believe it is >> probably a simple matter of >> clock speed. This server has 8 cores but they are clocked at 2 gigs. My >> previous server had only 4 >> cores but they were clocked at 3.2 gigs. The VM has always shown the best >> results with a single >> core and if the core is faster... >> >> So I will likely rebuild a server to just hold the vm. >> >> -- >> John W. Colby >> www.ColbyConsulting.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 > >