SQL Table Row Size

If you ever designed a table, you must have thought how much space this table will take on disk, memory, how can I estimate the amount of memory for the server etc'. well, I can't help you there, there are too many things to take into consideration to write in a short blog post.


However, I can help you with one thing, estimating the size of a row, just double it with the amount of rows you estimate will be in the table and you have a pretty close number, but you do have to know which indexes you'll have, they might even double the amount of space needed, this article will explain in detail how you can get a more accurate estimate.


Thanks to Ruchir T, who partially implemented this article, I just needed to make a few modifications and voila, you can now start estimating.

---- =============================================
---- Author: Ruchir T (http://www.west-wind.com/weblog/posts/2004/Jan/19/Sql-Server-Row-Size-Limit)
---- Create date: 01/02/2008
---- Update date: 2012-03-14 show all row info
---- Description: returns the number of bytes left to use for creating new columns
---- =============================================
CREATE FUNCTION usfTable_estimates
(    
    @tablename char(50)
)
RETURNS 
    @rettable table
    (
        TableName nvarchar(255),
       num_columns int,
       num_fixed_columns int,
       fixed_data_size int,
       num_var_columns int,
       max_var_size int,
       var_data_size int,
       null_bitmap_size int,
       row_size int,
       bytes_available int
    ) 
AS
begin 


DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype


-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size

--RETURN @result

insert into @rettable
select @tablename as TableName,
       @num_columns as num_columns,
       @num_fixed_columns as num_fixed_columns,
       @fixed_data_size as fixed_data_size,
       @num_var_columns as num_var_columns,
       @max_var_size as max_var_size,
       @var_data_size as var_data_size,
       @null_bitmap_size as null_bitmap_size,
       @row_size as row_size,
       @result as bytes_available
       
      return
    
end
GO

Read More

Tags: planning, sql

Dror Gluska March 14, 2012  No comment

SQL Table Space

I was doing capacity planning the other day and I needed to know which tables take how much space, how many rows they have etc'.

There's a system stored procedure for that, sp_spaceused, but it only works on the whole database or a specific table and i tried pushing its results into a table and sorting by it, but it converts the numbers to a string so the order didn't exactly worked.

so I looked in the sp's innards and wrote the following:

select stats.name,
      row_count,
       stats.reserved_page_count * 8 as reservedKB,
       stats.pages * 8 as dataKB,
       (CASE WHEN stats.used_page_count > stats.pages THEN (stats.used_page_count - stats.pages) ELSE 0 END) * 8 as index_sizeKB,
       (CASE WHEN stats.reserved_page_count > stats.used_page_count THEN (stats.reserved_page_count - stats.used_page_count) ELSE 0 END) * 8 as unusedKB
from
(
    select name, 
           
            sum(reserved_page_count) as reserved_page_count, 
            sum(used_page_count) as used_page_count, 
            sum(
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE lob_used_page_count + row_overflow_used_page_count
                END
                ) as pages
                
    FROM sys.dm_db_partition_stats
    join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
        WHERE sys.objects.type = 'U'
    group by name,sys.dm_db_partition_stats.object_id
) as stats
join
(
    select name, max(row_count) as row_count
    FROM sys.dm_db_partition_stats
        join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
            WHERE sys.objects.type = 'U'
    group by name
) as rowcountstats
    on rowcountstats.name = stats.name

order by row_count desc

Read More

Tags: dm_db_partition_stats, rows, sp_spaceused, space, sql

Dror Gluska March 06, 2012  No comment

Rebuild database

While still in design and development, a database goes through many changes, tables and columns are added and removed, records are added and deleted, sometimes performance tests are performed, a data structure goes through sanity testing, indexes get fragmented, etc'.


this leaves a lot of junk in the database files, sometimes this space is reclaimed by the SQL engine and sometimes you need to ask for it specifically, that is the purpose of this stored procedure.


I've used portions of it over the years but only this time I've decided to collect all of it into a single stored procedure, the dynamic nature of the current project I'm working on makes me execute this once in a while. I thought I'd share this with you, please be aware that this should not be executed in a production environment, you must read and understand what its doing before you execute it, it might cause some serious data loss in specific cases.


To help you avoid making that mistake, I've commented all the contents of the stored procedure and put a return in the beginning of it. 

-- =============================================
-- Author:  Dror Gluska
-- Create date: 2012-03-05
-- Description: Performs cleanup/rebuild on all indexes, freeing space and accelerating queries
-- =============================================
create PROCEDURE [dbo].[usp_DBCCCleanup]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 SET NOCOUNT ON;

    return;

----rebuild tables

--print 'Rebuilding tables...'

--declare @rebuildtables table(name nvarchar(255), cmd nvarchar(max));

--insert into @rebuildtables
--SELECT  o.[name],'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD ;'
--FROM sys.objects AS o WITH (NOLOCK)
--INNER JOIN sys.indexes AS i WITH (NOLOCK)
--ON o.[object_id] = i.[object_id]
--INNER JOIN sys.schemas AS s WITH (NOLOCK)
--ON o.[schema_id] = s.[schema_id]
--INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
--ON i.[object_id] = ps.[object_id] AND ps.[index_id] = i.[index_id]
--WHERE o.[type] = 'U' ORDER BY ps.[reserved_page_count]

--declare c_rebuild cursor for select name, cmd from @rebuildtables

--open c_rebuild

--declare @tname nvarchar(255), @tcmd nvarchar(max);

--fetch next from c_rebuild into @tname, @tcmd
--while (@@fetch_status <> -1)
--begin
-- print 'Rebuilding ' + @tname
-- exec sp_executesql @tcmd
 
-- fetch next from c_rebuild into @tname, @tcmd
--end
--close c_rebuild
--deallocate c_rebuild


--print 'Done rebuilding tables'

--print 'Rebuilding indexes...'
----taken from http://technet.microsoft.com/en-us/library/bb838727(v=office.12).aspx

--DECLARE @objectid int;
--DECLARE @indexid int;
--DECLARE @partitioncount bigint;
--DECLARE @schemaname nvarchar(130);
--DECLARE @objectname nvarchar(130);
--DECLARE @indexname nvarchar(130);
--DECLARE @partitionnum bigint;
--DECLARE @partitions bigint;
--DECLARE @frag float;
--DECLARE @command nvarchar(4000);
--DECLARE @dbid smallint;

---- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
---- and convert object and index IDs to names.

--SET @dbid = DB_ID();

--SELECT
--    [object_id] AS objectid,
--    index_id AS indexid,
--    partition_number AS partitionnum,
--    avg_fragmentation_in_percent AS frag, page_count
--INTO #work_to_do
--FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
--WHERE
----avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation
----AND
--index_id > 0 -- Ignore heaps
----AND page_count > 25; -- Ignore small tables
---- Declare the cursor for the list of partitions to be processed.
--DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
---- Open the cursor.
--OPEN partitions;
---- Loop through the partitions.
--WHILE (1=1)
--BEGIN
--FETCH NEXT
--FROM partitions
--INTO @objectid, @indexid, @partitionnum, @frag;
--IF @@FETCH_STATUS < 0 BREAK;
--SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
--FROM sys.objects AS o
--JOIN sys.schemas as s ON s.schema_id = o.schema_id
--WHERE o.object_id = @objectid;
--SELECT @indexname = QUOTENAME(name)
--FROM sys.indexes
--WHERE object_id = @objectid AND index_id = @indexid;
--SELECT @partitioncount = count (*)
--FROM sys.partitions
--WHERE object_id = @objectid AND index_id = @indexid;
---- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
--IF @frag < 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
--IF @frag >= 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
--IF @partitioncount > 1
--SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
--print 'Rebuilding/Reogranizing index ' + @indexname
--EXEC (@command);
--PRINT N'Executed: ' + @command;
--END
---- Close and deallocate the cursor.
--CLOSE partitions;
--DEALLOCATE partitions;
---- Drop the temporary table.
--DROP TABLE #work_to_do;
--print 'Updating usage'
--DBCC UPDATEUSAGE (0)
--print 'Shrinking database'
--declare @shcmd nvarchar(255) = 'DBCC SHRINKDATABASE (' + DB_NAME() + ',10)'
--exec (@shcmd)
 
END

Read More

Tags: sql

Dror Gluska March 05, 2012  No comment

Book Review: Scalability Rules: 50 Principles for Scaling Web Sites

If this article hurts anyone's copyright, let me know and I'll remove it.

Scalability Rules: 50 Principles for Scaling Web Sites - on Amazon


This book was a nice read, I didn't get overexcited about it.


The book is talking about what can and can't, what should and shouldn't be done to make your application and services easier to scale, I'm including a little summary so you can judge for yourself if you want to read it.


The following is my understanding and summary of the rules and do not completely correspond with the author's, I've omitted a few rules which looked redundant to me.


Rule 1 - Write simple programs, complicated programs are hard to maintain and hard to add scaling logic to.
Rule 2 - Design the program to scale up front, its cheaper than trying to scale a finished product.
Rule 4 - Balance CDNs and DNS lookups.
Rule 5 - Reduce objects, on HTML pages, CSS files, JS files, on page creation, in code etc', remember if an object is being created it also needs to be disposed of.
Rule 6 - Use a single hardware provider, less chance of collision between protocol implementations and standards. 
Rule 7 - Horizontally scale, for example, if you're using SOA, create some services and balance the load.
Rule 8 - Split the load of the program to different components.
Rule 11 - Use small abandundly available components rather than big specialized systems, e.g. horizontally scale.
Rule 12 - Design your data to be split across data centers.
Rule 13 - Design your application to utilize what clouds have to offer.
Rule 14 - Use RDBMS where best, NOSQL where best and file-systems where best, don't force one of them to be the other.
Rule 15 - Use firewalls on every important component you're using.
Rule 16 - Use log files, monitor them, analyze them.
Rule 17 - Don't check and read what your program just did, for example, read a file you just wrote or read a transaction you just committed.
Rule 18 - Avoid using redirects, its slowing the user's experience.
Rule 19 - Avoid state constaints.
Rule 21 - Implement expires headers, otherwise caching is limited.
Rule 22,23,24,25,26 - Cache ajax requests, pages, application object, db executions, service calls, use external cache storages like memcache.
Rule 27 - learn from everything, customers, etc'
Rule 28 - Don't rely on QA, the software engineers should do most of the testing, integrate tests into the program.
Rule 29 - Design to rollback your changes, not designing for it could be a disaster.
Rule 30 - Discuss failures, no need to blame, just learn from them.
Rule 31 - Check database relationships and constraints for load, cost, normalization, etc'.
Rule 32 - Use the right database locks where they are needed. page, row, table, schema, etc'.
Rule 33 - Do not use multiphase/two-phase commits.
Rule 34 - Avoid database cursors.
Rule 35 - Do not use select *, get only the data you need.
Rule 36 - Segregate program and data so if one part of the application goes down it doesn't take the whole system with it.
Rule 37 - Single points of failure (SPOFs) will fail, eliminate or plan for it.
Rule 38 - Avoid putting systems/components in series, slows things down and has the potantial of being a domino effect of failure.
Rule 39 - Add the ability to turn on and off features in your application without recompiling the program, if one coponent acts up, you can turn it off without affecting other components until its fixed.
Rule 40 - make everything as stateless as possible.
Rule 41 - Use cookies for state instead of server state.
Rule 42 - Use distributed cache for state, its easier to scale than application state.
Rule 43 - Use async calls instead of sync calls which freeze the program until something else is finished.
Rule 44,45 - Use message buses that can scale and only where the cost to perform the action is higher than the cost to process it via message bus.
Rule 46 - Avoid using 3rd party to scale your application, it might introduce more problems than it will solve.
Rule 48 - Don't use business intelligence in your transactions.
Rule 49 - design your application for monitoring, add logs, performance counters, etc'.
Rule 50 - don't blame anyone, to the user you're the one to blame, for example, if your hardware vendor is giving you a hard time solve it or replace them.

Read More

Tags:

Dror Gluska December 10, 2011  No comment

Comparing database schemas

WARNING: DBComparer comes with babylon toolbar and can't be removed by conventional ways. 

I've considered removing that post because of that but decided on adding a warning instead, to remove the babylon forced installation you'll have to get into firefox, ie and chrome, delete the new search engine and remove the default pages, in firefox you can do about:config, type babylon in the search box and delete all values.

---> Original post below.


So, you've been working on optimizing a database, stored procedures, indexes, views, you changed some of those, deleted a few and created new ones. You wrote on the side everything you did in the development database but somehow when you try to stage everything, you're not getting the desired performance or getting some exceptions about schema not being consistent. 

Now what?

Back in the days there was a project on sourceforge that made comparing database schemas a breeze, it was called dabcos. then came SQL 2008 and it stopped working, something about a version not being right. so I wrote a small override. then a new job came and I've lost the override and... no, I didn't have the time to look into it again.

So I've looked for a different option and that option is called DBComparer.

Read More

Tags:

Dror Gluska November 03, 2011  No comment

jQuery general ajax error

Over the years I've collected many code snippets to make programming easier, here's a small one that handles general ajax errors and shows them in a window, you'll need to design your own CSS for this to show properly.

 
//Attach global juery ajaxerror
$(window).ready(function ()
{
    $(document).ajaxError(function (e, jqxhr, settings, exception)
    {
        showError("Ajax Error - " + exception.toString(), jqxhr.responseText);
    });
});
 
//Hides the error window
function hideError()
{
    $('#mask').hide();
    $('#errorWindow').hide();
}
 
//Shows the error window.
function showError(strTitle, strMessage)
{
    //avoid showing an empty ajax message
    if ((strTitle == "Ajax Error - ") && (strMessage == ""))
    {
        return;
    }
 

    var mask = $('#mask');
 
    if (mask.length == 0) {
        mask = $('<div id="mask" class="windowMask"></div>');
        $("body").prepend(mask);
    }
 
    //Get the screen height and width
    var maskHeight = $(document).height();
    var maskWidth = $(window).width();
 
    //Set height and width to mask to fill up the whole screen
    mask.css({ 'width': maskWidth, 'height': maskHeight });
 
    //transition effect  
    mask.fadeIn(1000);
    mask.fadeTo("slow", 0.8);
 
    //Get the window height and width
    var winH = $(window).height();
    var winW = $(window).width();
 
    var errorWindow = $('#errorWindow');
    if (errorWindow.length == 0) {
        errorWindow = $('<div id="errorWindow" class="windowError"></div>');
        $("body").prepend(errorWindow);
    }
 

    errorWindow.html('<div class="windowHeader">' + strTitle + '</div><div class="windowClose" onclick="hideError();">Close</div>' + '<div class="windowContent">' + strMessage + '</div>');
 
    //Set the popup window to center
    $(errorWindow).css('top', winH / 2 - $(errorWindow).height() / 2);
    $(errorWindow).css('left', winW / 2 - $(errorWindow).width() / 2);
 
    //transition effect
    $(errorWindow).fadeIn(2000);
}
 
 

Read More

Tags: ajax, error, javascript, jquery

Dror Gluska July 07, 2011  No comment

Timesheet

One of the drawbacks of having flexible work hours is when forgetting to punch in when you enter the office. later during the week when you try to remember when did you arrive could be hard and I prefer to offload it from myself.

I don't know about you, but usually the first thing I do when I enter the office and go to my seat and unlock the computer and the last thing is locking the computer.

My solution? write an application that logs these events.

So, what can we learn from that application?

SessionSwitch event which passes SessionSwitchReason that can tell you what happened, if the station was locked, unlocked, remote desktop connected, disconnected which was enough for me, I wanted the application to log whenever I lock the station since I don't usually shutdown the computer at the end of the day and i wanted it to log whenever I connect remotely in the morning so if I work from home that day, I can get that logged too.

We can get the currently logged in username with WindowsIdentity.GetCurrent()

Invoking methods asynchronously with MethodInvoker

Accelerating LINQ to objects queries with PLINQ's AsParallel

Interlocked.CompareExchange as a simple way of checking if a method is already executing.

Final thoughts - 

I wrote the initial application a long time ago, but I've recently overhauled the the whole application so it will be more presentable, there are still some logical problems, such as the daily calculations and weekly calculations sometimes show incorrect data and there are some extreme situations when something is not logged consistently it will show wrong numbers, but this is good enough for me and not worth spending more time, I'm mostly using the logging function, the calculations are just for getting a rough number.

This is a toy for myself, I just thought to share it, if you need any kind of reliability or tracking, this toy is not for you.

https://github.com/drorgl/ForBlog/tree/master/Timesheet

Read More

Tags:

Dror Gluska July 04, 2011  No comment

Using Razor Templates In Your Application

We needed a templating engine that will work in a medium trust environment, after reviewing StringTemplate and NVelocity and we came to the conclusion that Razor can do all we need and more, plus it comes with the framework so no need for external dependencies.

You should be aware that Razor is compiling .NET code, it can and will create security breeches in your application if you allow users to change the templates, you can alleviate some of these security issues by segregating your code and giving the razor section access only to the parts it needs, think this through.

The project contains a few important parts.
1. It should have its own TemplatesController, its just an empty controller for the engine to run against.
2. Templates views directory, this is where we're storing the templates for the engine to execute.
3. Templates.cs is where some of the magic happens.
4. For the sake of the demo, I've added a custom WebViewPage called RazorBaseWebViewPage and a SimpleModel.


Templates.cs contains the following:
1. Execute - executes a view against a controller, with ViewData and Model.

/// <summary>
/// Generates a controller and context, hands them off to be rendered by the view engine and 
/// returns the result string
/// </summary>
/// <param name="viewName">Template Name</param>
/// <param name="model">Model for the view</param>
/// <param name="viewData">ViewData</param>
/// <returns>rendered string</returns>
private static string Execute(string viewName, ViewDataDictionary viewData, object model)
{
    var controller = new TemplatesController();
    controller.ControllerContext = new ControllerContext();
    controller.ControllerContext.HttpContext = new HttpContextWrapper(HttpContext.Current);
    controller.RouteData.DataTokens.Add("controller", "Templates");
    controller.RouteData.Values.Add("controller", "Templates");
    controller.ViewData = viewData;
    return RenderView(controller, viewName, model);
}

2. GetViewName - gets or writes a new template to the templates directory, it uses a hash of the template for the first part of the filename. Same trick as a hash table.

/// <summary>
/// Retrieves the view name by template and model
/// </summary>
private static string GetViewName(string template,Type modelType)
{
    //gets the razor template from a text template
    var razortemplate = GetViewContentFromTemplate(template, modelType);

    //gets the hash string from the razor template
    string hashstring = BitConverter.ToString(BitConverter.GetBytes(razortemplate.GetHashCode()));

    //check if view exists in folder
    var files = Directory.GetFiles(ViewDirectory, hashstring + "*.cshtml");
    foreach (var file in files)
    {
        if (File.ReadAllText(file, Encoding.UTF8) == razortemplate)
            return Path.GetFileNameWithoutExtension(file);
    }

    //if not, add it
    string filename = Path.Combine(ViewDirectory, hashstring + "_" + Guid.NewGuid().ToString() + ".cshtml");
    File.WriteAllText(filename, razortemplate,Encoding.UTF8);

    return Path.GetFileNameWithoutExtension(filename);
}

3. RenderView - calls the razor engine's Render. executed from Execute. (Origin)

/// <summary>
/// Renders a PartialView to String
/// </summary>
private static string RenderView(Controller controller, string viewName, object model)
{
    //origin http://craftycodeblog.com/2010/05/15/asp-net-mvc-render-partial-view-to-string/
    if (string.IsNullOrEmpty(viewName))
    {
        return string.Empty;
    }

    controller.ViewData.Model = model;
    try
    {
        StringBuilder sb = new StringBuilder();
        using (StringWriter sw = new StringWriter(sb))
        {
            IView viewResult = GetPartialView(controller, viewName);
            ViewContext viewContext = new ViewContext(controller.ControllerContext, viewResult, controller.ViewData, controller.TempData, sw);
            viewResult.Render(viewContext, sw);
        }
        return sb.ToString();

    }
    catch (Exception ex)
    {
        return ex.ToString();
    }
}

4. Render - the exposed method to do the actual rendering.

/// <summary>
/// Renders a template with parameters to string
/// </summary>
/// <param name="template">template text to render</param>
/// <param name="model">the model to give the template</param>
/// <param name="parameters">the ViewData for the execution</param>
/// <returns>rendered template</returns>
public static string Render(string template, object model, ViewDataDictionary parameters)
{
    //if empty
    if (string.IsNullOrEmpty(template))
        return string.Empty;

    //if doesn't contain razor code
    if (template.IndexOf("@") == -1)
        return template;

    //get View filename
    string fileName = GetViewName(template, (model != null) ? model.GetType() : typeof(object));

    //Execute template
    return Execute(fileName, parameters, model);
}

I've ran some analysis on the code's performance, a few places might be helpful to optimize is the GetPartialView and GetViewName are slow.

You can find the project here:
https://github.com/drorgl/ForBlog/tree/master/RazorTemplateDemo

Read More

Tags: C#, razor, templates

Dror Gluska May 16, 2011  No comment

SQL Query Usage

Sometimes when trying to find out the cause of a high load on a SQL server, you need to find out what is executing and taking its resources, luckily SQL keeps track of query usage and you can query those statistics.


If you're lucky (or not, depending on your point of view), you might be able to catch these queries in the act, Pinal Dave helped me to do it the first time. This query will show you the currently executing queries.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Elisabeth Redei made my life very easy when she wrote this query, its been with me for quite a while, it will show you the queries using the most resources, you can order by whatever you need to know, and you can uncomment the where code to find specific queries.

--select * from sys.dm_exec_query_stats
SELECT

 (
  total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
  , max_elapsed_time/1000 AS [MaxExecTime in ms]
  , min_elapsed_time/1000 AS [MinExecTime in ms]
  , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
  , qs.execution_count AS NumberOfExecs
  , (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
  , max_logical_reads AS MaxLogicalReads
  , min_logical_reads AS MinLogicalReads
  , max_logical_writes AS MaxLogicalWrites
  , min_logical_writes AS MinLogicalWrites
  , qs.last_execution_time
  ,
   (
    SELECT SUBSTRING(text,statement_start_offset/2,
     (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2
      ELSE statement_end_offset
     end -statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)
    ) AS query_text

FROM sys.dm_exec_query_stats qs
--where(
--    SELECT SUBSTRING(text,statement_start_offset/2,
--     (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2
--      ELSE statement_end_offset
--     end -statement_start_offset)/2)
--    FROM sys.dm_exec_sql_text(sql_handle)
--    ) like '%insert%'
ORDER BY [Avg Exec Time in ms] DESC

Read More

Tags: sql, statistics

Dror Gluska April 26, 2011  No comment

Compiled string.Format

Ever looked at Performance wizard and seen a significant potion being taken by string.Format? one day I have and decided to try and find a faster string.Format, it took a couple of hours and I came up with a way to cache the static and dynamic portions of the string which speed up things by a bit, but not enough to permanently integrate it into the project, maintenance time is not worth it. 


But if you're program relies heavily on string.format and the difference you have with 1 million executions is worth the 100-500 ms you'll save on it, have fun.


For example, a formatted string with 5 parameters times 1 million executions is ~2350 ms with my method and ~2800 ms with string.Format.


You can find the project here:
https://github.com/drorgl/ForBlog/tree/master/FormatBenchmarks


and the benchmarks here:
http://uhurumkate.blogspot.com/p/stringformat-benchmarks.html

The categories in the graph are a number of parameters the formatting needs to parse.

Read More

Tags: benchmarks, C#, string.format

Dror Gluska March 21, 2011  No comment