This query shows: Amount of memory allocated to the buffer pool Amount of memory consumed by BPool Amount of memory used by AWE [cc lang=”sql”] SELECT SUM(single_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] FROM sys.dm_os_memory_clerks WHERE type = ‘MEMORYCLERK_SQLBUFFERPOOL’ [/cc]
Continue reading ...
Below is a technique used to transfer a large amount of records from one table to another. This scales pretty well for a couple reasons. First, this will not fill up the entire log prior to committing the transaction. Rather, it will populate the table in chunks of 10,000 records. Second, it’s generally much quicker. You will have to play around with the batch size. Sometimes it’s more efficient at 10,000, sometimes 500,000, depending on the system. If you do not need to insert into an existing table and just need a copy of the table, it is better to do a SELECT INTO. However for this example, we are inserting into an existing table. Another trick you should do is to change the recovery model of the database to simple. This way, there will be much less logging in the transaction log. The WITH (TABLOCK) below only works in SQL 2008. [cc lang=”sql”] DECLARE @BatchSize int = 10000 WHILE 1 = 1 BEGIN INSERT INTO [dbo].[Destination] –WITH (TABLOCK) — Uncomment for 2008 ( FirstName ,LastName ,EmailAddress ,PhoneNumber ) SELECT TOP(@BatchSize) s.FirstName ,s.LastName ,s.EmailAddress ,s.PhoneNumber FROM [dbo].[Source] s WHERE NOT EXISTS ( SELECT 1 FROM dbo.Destination WHERE PersonID = s.PersonID ) IF @@ROWCOUNT < @BatchSize BREAK END [/cc] With the above example, it is important to have at least a non clustered index on PersonID in both tables. Another way to transfer records is to use multiple threads. Specifying a range of records as such: [cc lang="sql"] INSERT INTO [dbo].[Destination] […]
This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) select * FROM SQLPaging where ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc]
Continue reading ...
This post deals with a random hanging that sometimes happens with SQL Server 2005+. In order to troubleshoot SQL Server Slowness, go here. The introduction of the new SQL Server 2005 Query Optimization engine has brought great things (including statement-level caching and smarter execution plan generation). There is however a little more overhead with the advent of this new technology. Aside from taking longer to generate an execution plan, I have noticed two separate instances where a query would appear to intermittently hang. From a database engine perspective however, the query is not hanging but generating an execution plan. The two instances I’ve witnessed this in were in both SQL Server 2005 (sp2), and also now in SQL Server 2008. Both procedures were relatively small, however somewhat complex in their where clauses. The physical indicators in both instances where very high CPU usage and very low IO usage for one particular SPID which we gathered by executing sp_who2. Here is an example of the query that hung on SQL Server 2008: [cc lang=”sql”] SET @IsTrue = ( SELECT COUNT(1) FROM dbo.table1 t1 JOIN dbo.table2 t2 ON t1.ColumnID = t2.ColumnID WHERE ( t2.ID = @ID AND t2.SomeDate < GETDATE() ) OR EXISTS ( SELECT 1 FROM dbo.Table3 WHERE ID = @ID AND AlternateID IN (1,2,3,4,6) ) ) [/cc] Once the query would hang in generating the execution plan, the effect seemed to snowball and cause other executing instances of the same procedure to hang. Also, no blocking was occuring in the […]
