I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 176, comments - 1796, trackbacks - 33

My Links

SQLTeam.com Links

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer. I'm a MCP and MCTS for SQL Server. I also speak at local user group meetings and conferences like NT Conference 
Welcome to my blog.

Search this Blog

My Blog Feed via Email


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Monday, June 15, 2009

SQL Server 2008 for Developers live meeting presentation

I’ll be talking about the following topics:

  • What should developers know about database design so they don't have performance and logical problems?
  • What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before.
  • Concurrency design models and isolation levels.

     

    The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET

    More info at PASS Application Development SIG

  •  

    UPDATE: you can view the presentation here.

    posted @ Monday, June 15, 2009 11:22 PM | Feedback (0)

    Friday, June 05, 2009

    Adding one file to multiple projects in a .Net solution

    There are times when you want to have one .cs file in multiple projects. However if you do Add existing item you’ll notice that the file is copied to each project’s folder. This is not what we want.

    The solutions is of course pretty simple once you know where to look. In the Add existing item dialog you have to add the file as a Link as is shown on the picture:

     

    AddCommonItem

    And there you go. you can have one file in multiple projects.

    kick it on DotNetKicks.com

    posted @ Friday, June 05, 2009 4:03 PM | Feedback (1)

    Friday, May 29, 2009

    SQL Server 2008 Extended Events - high performance eventing system

    I’ve written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

     

    1. Introduction to SQL Server 2008 Extended Events

    This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture, new terminology and we try them out with a simple example.

     

    2. Advanced SQL Server 2008 Extended Events with Examples

    Part 2 of the series takes a look at some performance considerations we must be aware of when using Extended Events like synchronous or asynchronous target target, predicate short circuiting and event action size. After we take a look at 7 examples that show a different way we can use them to troubleshoot our system. Examples are meant to show Extended Events power and give some ideas on how to use them for more advanced monitoring.

     

    kick it on DotNetKicks.com

    posted @ Friday, May 29, 2009 4:15 PM | Feedback (0)

    Thursday, May 07, 2009

    Free SQL Server 2008 Powerpoint dark template

    Recently i was looking for a SQL Server 2008 based template that would look cool. Since I haven’t found anything I’ve made my own. It’s a dark background template that you can use freely.

    Suggestions for improvements are welcome in the comments. Also if you have an idea for a light background based template, I’d love to hear it.

    The title look

    The slide look

    Presentation_Dark_Title

    Presentation_Dark_Slide

     

    Here you go: SQL Server 2008 Dark Template.

    Enjoy it!

    kick it on DotNetKicks.com

    posted @ Thursday, May 07, 2009 1:24 PM | Feedback (1)

    Tuesday, April 28, 2009

    Comparing SQL Server HASHBYTES function and .Net hashing

    A while back we had an interesting problem at work. We were calculating MD5 hashes for some values in both .Net and SQL Server and although the input values were all the same our MD5 hashes were different. After some time spent looking dumbfounded at the code I’ve realized what the bug was and started laughing. I saw that HashBytes function was the “culprit”. HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats. HashBytes' result depends on the input text’s data type and can at the first glance give a bit different results that we might think, especially when combined with the .Net framework. Of course hashing itself works correctly in both. Let’s see what going on with some examples. From here on when i say hash i mean MD5 hash.

    Example in SQL Server

    -- note the size 15 of all datatypes
    declare @val1 varchar(15),
            @val2 nvarchar(15),
            @val3 char(15),
            @val4 nchar(15)
    
    -- example of 1 byte/char text
    -- all variables are of same length
    select  @val1 =  '1234567890',
            @val2 = N'1234567890',
            @val3 =  '1234567890',
            @val4 = N'1234567890'
    
    -- all 4 return different results
    select  HASHBYTES('md5', @val1) as MD5_varchar,     -- result = 0xE807F1FCF82D132F9BB018CA6738A19F
            -- just to show that collation doesn't change the hash
            HASHBYTES('md5', @val1 collate Cyrillic_General_BIN2) as MD5_varchar_collation, -- result = 0xE807F1FCF82D132F9BB018CA6738A19F
            HASHBYTES('md5', @val2) as MD5_Nvarchar,    -- result = 0xE15E31C3D8898C92AB172A4311BE9E84
            HASHBYTES('md5', @val3) as MD5_char,        -- result = 0x2120C3F3423F89BA8A65ABD933321884
            HASHBYTES('md5', @val4) as MD5_Nchar        -- result = 0x90DEF5840F3A31174CA44E2022F743B6
    
    -- example of 2 bytes/char text
    -- 中文 means Chinese in written text
    -- converting 2 bytes/char text to varchar cuts the text in half
    select  @val1 =  '中文',   -- this cuts the text in half
            @val2 = N'中文',   -- this stores the whole text
            @val3 =  '中文',   -- this cuts the text in half
            @val4 = N'中文'    -- this stores the whole text
    
    -- all 4 return different results
    select  HASHBYTES('md5', @val1) as MD5_varchar,     -- result = 0xEA03FCB8C47822BCE772CF6C07D0EBBB
            HASHBYTES('md5', @val2) as MD5_Nvarchar,    -- result = 0x73C6C8CD2F94355EF015E5265D5E65B1
            HASHBYTES('md5', @val3) as MD5_char,        -- result = 0xA13C45A38853677887B4839071537634
            HASHBYTES('md5', @val4) as MD5_Nchar        -- result = 0xEADEBD3BD72A481C43C828E0C550145C
     
    The catch here is the data type difference. Nvarchar and nchar take twice more bytes to store data than varchar and char. Since char and nchar pad the right side of the string with spaces to fill the gap up to defined data type length the spaces are also used in MD5 calculation. A collation plays no part in calculating hash values since collations are only applied to sorts and comparisons.

    However care must be taken with texts that need 2 bytes per char of storage space like Chinese text. Storing it in a varchar variable cuts it in half.

    Example in .Net - C#

    For generating MD5 hashes I’ve used the code from this site which turned up as the first result on Google for “MD5 in C#”:

    public string GetMD5Hash(string input)
    {
        System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
        //byte[] bs = System.Text.Encoding.ASCII.GetBytes(input);
        //byte[] bs = System.Text.Encoding.UTF7.GetBytes(input);
        //byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);
        byte[] bs = System.Text.Encoding.Unicode.GetBytes(input);
        //byte[] bs = System.Text.Encoding.UTF32.GetBytes(input);
        bs = x.ComputeHash(bs);
        System.Text.StringBuilder s = new System.Text.StringBuilder();
        foreach (byte b in bs)
        {
            s.Append(b.ToString("x2").ToLower());
        }
        string password = s.ToString();
        return password;
    }
    
    // hashes for the simple “1234567890” text in all encodings
    ASCII:   e807f1fcf82d132f9bb018ca6738a19f
    UTF7:    e807f1fcf82d132f9bb018ca6738a19f
    UTF8:    e807f1fcf82d132f9bb018ca6738a19f
    Unicode: e15e31c3d8898c92ab172a4311be9e84
    UTF32:   6a57502c29a5081f03cb70e0ad38ecc7
    
    // hashes for the complex “中文” text in all encodings
    ASCII:   ea03fcb8c47822bce772cf6c07d0ebbb
    UTF7:    eb02105e5c51a33f21e8da7f8102cfda
    UTF8:    a7bac2239fcdcb3a067903d8077c4a07
    Unicode: 73c6c8cd2f94355ef015e5265d5e65b1
    UTF32:   65fe91b81ed1107566f9f9f5ed4ccaf1 

     

    All strings in .Net store their chars in 2 bytes by default. When hashing values we have to take this into account and use proper text Encoding. .Net supports 5 encodings: ASCII (7 bits per char), UTF7 (7 bits per char), UTF8 (8 bits = 1 byte per char), Unicode (UTF-16) (16 bits = 2 bytes per char) and UTF32 (32 bits = 4 bytes per char).

    For text with only first 127 chars in the ASCII table ASCII, UTF7 and UTF8 encodings all return the same hash, but with UTF16 and UTF32 comes endianness so they don’t return the same hash values. Also note that the ASCII and UTF7 encoding aren’t recommended to be used anymore except in legacy apps. UTF8 should be used instead.

    For text with complex chars we have to use Unicode or UTF32 or we loose char information.

    Putting .Net and SQL Server together

     

    It turns out that such a simple thing as hashing can become a serious issue if we’re not careful. Trouble always awaits when dealing with text and encodings. :)

    When using SQL Server’s varchar data type the .Net encoding to go with is UTF8 since it’s the fastest and most optimized of the three (ASCII, UTF7, UTF8). When using the nvarchar data type to go is Unicode (UTF16) but we also have to know the texts endianness to create correct hashes. UTF32 is practically useless in this case because SQL Server doesn’t have a data type that stores text in 4 bytes/char so we’ll never get the same results if we use it.

    This advice only applies when creating hashes both in .Net and SQL server and comparing them. If we’re creating hashes in .Net and only store them in a database then we don’t have to worry about this.

     

    kick it on DotNetKicks.com

    posted @ Tuesday, April 28, 2009 11:43 AM | Feedback (0)

    Wednesday, April 08, 2009

    SQL Server - Find missing and unused indexes

    Indexes are one of the most important database features. Without them your database will crawl under a table in fear of simple queries on large tables or complex queries on small tables. That’s why one of the most important things a DBA or a database developer should know is basic index maintenance.

    Performance problems are usually result of missing indexes. Index tuning is more of an art than it is a science since possible workloads are so many that there is no one rule to rule them all. It’s a mixture of testing, implementing, retesting and reimplementing. For this purpose The SQL Server team has created the Database Tuning Advisor that takes a recorded workload (from SQL Profiler), analyzes it and suggests index improvements. However this is a tool for some really deep analysis, so we’ll take a look at some lightweight options.

    On the other hand too many unused indexes bloat the database and unnecessarily increase the database size. This can cause problems with larger backups that take longer to create. This is why there’s a delicate balance to keep when maintaining indexes.

    With SQL Server 2005 we got introduced to Dynamic Management Views (DMV’s). They are a powerful tool when troubleshooting SQL Server performance. Their downside is that most of them hold aggregated values since server start. That’s why we have to poll them twice at the start and at the end of the desired interval to get meaningful results. However for our purpose we don’t have to do this since we want to know which indexes are not used since our server started. We can find this information because when building an execution plan the SQL Server keeps information about every index it has used and could have used because it’s a cost based engine.

     

    Finding missing indexes

    Needed Dynamic Management Views (DMV’s)

    As I said earlier SQL Server keeps data about possible missing indexes. If an actual execution plan is included when running a query then the missing indexes are also displayed there.

    The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:

    sys.dm_db_missing_index_groups

    This DMV returns only 2 columns with information about which indexes are in which group.

    sys.dm_db_missing_index_group_stats

    This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.

    sys.dm_db_missing_index_details

    This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.

    sys.dm_db_missing_index_columns

    This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.

     

    A simple example

    USE AdventureWorks;
    GO
    -- run some queries
    SELECT  City, ModifiedDate
    FROM    Person.Address
    WHERE   StateProvinceID < 1000 AND ModifiedDate > DATEADD(yyyy, -5, GETDATE());
    GO
    SELECT  City, StateProvinceID, PostalCode
    FROM    Person.Address
    WHERE   StateProvinceID = 15733;
    GO
    -- get the missing indexes that would be beneficial for speeding up above queries
    SELECT  D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
    FROM    sys.dm_db_missing_index_groups G
            JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
            JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
            CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
    ORDER BY D.index_handle, [statement];

     

    The DMV’s return raw data so you’ll have to do some string magic to build the CREATE INDEX statements out of it.

     

    Limitations

    This cool feature of course has some limitations. Some of those are:

    • Database Engine Tuning Advisor kicks its behind. Think of Missing Indexes DMV’s as a really lightweight DTA. So when doing a really in-depth index analysis, don’t rely only on the Missing Indexes DMV’s. Just start with them.

    • Missing Indexes DMV’s don’t provide any information about new index overhead like space or IO/CPU overhead on updates, inserts and deletes.

    • There’s no information about the column order in the suggested index or whether it should be clustered or non-clustered.

    • Missing Indexes DMV’s consider only per query indexes and not per workload indexes.

    • Missing Indexes DMV’s can track a maximum of 500 indexes.

    • Trivial execution plans (plans for really simple SQL Statements) are not considered.

     

     

    Finding unused indexes

    Since SQL Server keeps data of all used indexes, getting the unused indexes is a simple matter of comparing used indexes to all existing indexes. Those that exist but are not used are of course unused indexes.

    SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
            OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
            I.NAME AS IndexName        
    FROM    sys.indexes I   
    WHERE   -- only get indexes for user created tables
            OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 
            -- find all indexes that exists but are NOT used
            AND NOT EXISTS ( 
                        SELECT  index_id 
                        FROM    sys.dm_db_index_usage_stats
                        WHERE   OBJECT_ID = I.OBJECT_ID 
                                AND I.index_id = index_id 
                                -- limit our query only for the current db
                                AND database_id = DB_ID()) 
    ORDER BY SchemaName, ObjectName, IndexName 

     

    Note that both these methods are only useful if your server has been running long enough and has been through peek usage. The data in the mentioned DMV’s is cleared on the next instance restart. If we want to save this information for further analysis we should create a scheduled job that periodically queries the DMV’s and saves the information to tables.

     

    kick it on DotNetKicks.com

    posted @ Wednesday, April 08, 2009 11:46 PM | Feedback (4)

    Thursday, February 19, 2009

    What was my first computer and what were some of my favorite games?

    I was tagged by Denis Gobo in the latest blog meme: What was your first computer and games you’ve played?

     

    Well  mine was the Sinclair ZX Spectrum.

    I got it when i was 9 years old and I only used it to play games. No programming for this kid just yet. That came a lot later.

    This baby had a tape drive whose sound I really enjoyed imitating to annoy my mom :)

     

     

     

     

     

    So what were some of the games? These are the four I remember:

    Grand Prix Circuit

    This was my first intro into the world of F1. I can still remember the “eeeeee cccccc  beep beep beep” sounds of the engine and tires. Ahhhh, those were the days…

     

     

    The Chessmaster 2000

    My first chess program. I was taught chess at the age of 4 by my uncle and have played it more or less actively since. I remember staying up late for many of nights trying to beat it.

     Chessmaster_2000

     

    Frogger

    The one, the only, the ultimate - the frog! Up, down, all around, left, right, jump with might! Such simplicity yet so playable. Even today I like to play a game of Frogger now and then.

     

     

     

    Popeye

    This one I enjoyed playing but i haven no clue why. Probably to bring out my romantic side as the game was filled with hearts and winning the lady. :)

    That’s it.

     

    I’m tagging Jeff Smith, Peter Larsson, Adam Machanic and Robert C. Cain to tell us what they had and played with.

    posted @ Thursday, February 19, 2009 8:44 PM | Feedback (5)

    Monday, February 09, 2009

    SSMS Tools Pack 1.5 is out with Window Connection Coloring

    This new version brings one completely new feature and a few improved old ones.

    The new feature is Window Connection Coloring.
    It includes a colored strip indicator that can be docked to any side of the window.

    Improved features include:

    You can also take a look at the full feature list.

    Hope you enjoy the best free Add-In for SQL Server Management Studio out there!

    And if you're feeling extra generous there's always PayPal. After all i do have to pay for hosting and stuff, right? :)

     

    kick it on DotNetKicks.com

    posted @ Monday, February 09, 2009 1:10 PM | Feedback (22)

    Saturday, January 31, 2009

    The day Google “died” and I used Live search for an hour

    On Saturday 31st January 2009 at around 14:40 UTC something “historical” happened. Google virtually stopped working. It might as well died. Every single search result was marked with “This site may harm your computer.”. And it redirected you to a warning page.

    Of course this was probably because of some bug in their harmful content detection software or a failed upgrade or something similar. I doubt it was a virus. It doesn’t really matter why but for an hour I was forced to use Live search. Yahoo was just too much typing in the address bar. :)

     

    However this brings on an interesting thought. What would happen if this search outage happened on a Wednesday which is usually the busiest week day. This would result in huge loss of revenue and traffic for everyone. If we just take a look at this blog: 95% of all traffic comes from Google searches. Yep… that’s 95% !! And others have pretty much the same deal. You do the numbers for other sites that have several tens of thousands of users per day. This was a worldwide failure. USA, Central and South America, Europe and Asia (I haven’t talked with anyone from the African continent). You name it, it failed.

    And the best thing is that Google only provides a service you’re not committed to using so you can’t do anything about your lost revenue. And as much fun as this brought to people imagine the consequences on a larger scale.

     

    Today around 1.5 billion people use the internet. Of them on average 70% use Google Search. That’s a bit over 1 billion people. So let’s make a really pessimistic estimate that each of those people spend $1 per day on the internet. They spend it because they found something they wanted to buy. Using equal hourly distribution for the day (earth spins :) and Google was down 1 hour) we get little less than $42 million loss. And remember that’s just for $1 per person per day. There are no real statistics I could find on this but i wouldn’t be surprised if the real estimate was 10 times higher. Some might argue that people would just go search somewhere else. Wrong. If you’re reading this then you’re not an average internet user. An average internet user is set in their way. if something doesn’t work they go away and forget about it.

    I know Google’s slogan is Don’t be evil, but still… are you sure about that? Are you prepared to be totally dependent on them? What can you do about it? I know I have no clue, do you? They still provide me with 95% of traffic :)

     

    On to Live search. I have to say that they’re improving. I actually found what i was looking for there.

    But change is hard. It’s interesting to see how my eye and arm/mouse movements are synced with Google’s search layout. It simply hurts to switch. Ok… it doesn’t hurt but it’s still annoying.

    Which begs the question: “Why change your search engine at all?” I know I have no other reason then when the primary one fails.

    Search results were pretty much the same now that I can cross reference them on Google. Therefore if Microsoft has any plans to steal people form Google they’ll have to have some added value in their search. I have no clue as to what that might be, but I’m not a marketing guru. :)

    And of course here’s a picture to prove the “historical” event for posterity with a bit of irony:

    GoogleFail

    kick it on DotNetKicks.com
     

    posted @ Saturday, January 31, 2009 5:40 PM | Feedback (6)

    Monday, January 05, 2009

    The simplest way to delete duplicates and compare two result sets in SQL Server

    Comparing result sets

    There are times, although not often that you have to compare 2 result sets. This usually happens when you're analyzing data for whatever reason or unit testing a database.

    Usual methods include

    - using the UNION of both queries because it filters duplicate data and checking if the row counts are the same

    - using the EXCEPT if you have SQL Server 2005+

    - using NOT EXISTS which is very ugly and not worth it

    - using a FULL OUTER JOIN and looking at nulls or some other method I haven't mentioned

    - returning result sets to client and comparing them there

    - some other method I haven't mentioned

     

    But for a while now I've been using this method using BINARY_CHECKSUM, CHECKSUM and CHECKSUM_AGG functions, since they are available in all versions of SQL Server. I prefer BINARY_CHECKSUM over CHECKSUM because BINARY_CHECKSUM is case sensitive. In a case-insensitive database CHECKSUM returns the same value for 'abcd' and 'ABCD' while BINARY_CHECKSUM does not.

    Here's the code I use for result set comparison:

     

    USE AdventureWorks GO IF ( SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( -- first resultset to compare SELECT * FROM Person.Address ) t1 ) = ( SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( -- second resultset to compare SELECT * FROM Person.Address WHERE AddressID < 1000 ) t1 ) BEGIN SELECT 'Resultsets equal' END ELSE BEGIN SELECT 'Resultsets NOT equal' END

     

    Deleting duplicates

    Deleting duplicates becomes an easy task with this method both in SQL Server 2000 and 2005+:

    CREATE TABLE #t1(ID INT NULL, VALUE VARCHAR(2)) INSERT INTO #t1(ID, VALUE) VALUES (1,'aa') INSERT INTO #t1(ID, VALUE) VALUES (2,'bb') INSERT INTO #t1(ID, VALUE) VALUES (1,'aa') INSERT INTO #t1(ID, VALUE) VALUES (1,'aa') INSERT INTO #t1(ID, VALUE) VALUES (3,'cc') INSERT INTO #t1(ID, VALUE) VALUES (3,'cc') GO -- BINARY_CHECKSUM(<column names>): <column names> are columns that we want to compare duplicates for -- if you want to compare the full row then change BINARY_CHECKSUM(<column names>) -> BINARY_CHECKSUM(*) -- for SQL Server 2000+ a loop -- save checksums and rowcounts for duplicates SELECT BINARY_CHECKSUM(ID, VALUE) AS ChkSum, COUNT(*) AS Cnt INTO #t2 FROM #t1 GROUP BY BINARY_CHECKSUM(ID, VALUE) HAVING COUNT(*)>1 DECLARE @ChkSum BIGINT, @rc INT -- get the first checksum and set the rowcount to the count - 1 -- because we want to leave one duplicate SELECT TOP 1 @ChkSum = ChkSum, @rc = Cnt-1 FROM #t2 WHILE EXISTS (SELECT * FROM #t2) BEGIN -- rowcount is one less than the duplicate rows count SET ROWCOUNT @rc DELETE FROM #t1 WHERE BINARY_CHECKSUM(ID, VALUE) = @ChkSum -- remove the processed duplicate from the checksum table DELETE #t2 WHERE ChkSum = @ChkSum -- select the next duplicate rows to delete SELECT TOP 1 @ChkSum = ChkSum, @rc = Cnt-1 FROM #t2 END SET ROWCOUNT 0 GO SELECT * FROM #t1 -- for SQL Server 2005+ a cool CTE ;WITH Numbered AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ChkSum ORDER BY ChkSum) AS RN, * FROM ( SELECT BINARY_CHECKSUM(ID, VALUE) AS ChkSum FROM #t1 ) t ) DELETE FROM Numbered WHERE RN > 1; GO SELECT * FROM #t1 DROP TABLE #t1; DROP TABLE #t2;

    It's fast, simple, clean, understandable and works like a charm. If you have a better way do let me know.

     

    kick it on DotNetKicks.com
     

    posted @ Monday, January 05, 2009 2:50 PM | Feedback (7)

    Monday, December 29, 2008

    SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

    This is something I haven't noticed before but I think it's mighty cool!

    When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them.

    I'm guessing the number (green 84.7361 in the picture) is in percent... So it's like a Database Tuning Advisor "Lite" :))

    And best of all this also works when you're connected to a SQL Server 2000 or 2005.

     

    This is what it looks like:

    SuggestIndexExecPlan

     

    I'm starting to like this new SSMS more and more.

     

    kick it on DotNetKicks.com

    posted @ Monday, December 29, 2008 8:10 PM | Feedback (5)

    Wednesday, December 03, 2008

    Advanced SQL Server 2005 Express Job Scheduling

    I've written a second article here on SQL Team on how to schedule jobs in SQL Server 2005 Express.

    Intro

    In my previous article we saw how to make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough. In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself including run once, daily, weekly, monthly absolute and relative scheduling:

    Scheduling Jobs in SQL Server Express - Part 2

     

    And part 1 of this series:

    Scheduling Jobs in SQL Server Express - Part 1 

     

     

    kick it on DotNetKicks.com

    posted @ Wednesday, December 03, 2008 11:29 AM | Feedback (4)

    Monday, November 03, 2008

    I KILL-ed you! Now can I go for a coffee break while you die?

    The victim of my carnage being a connection to the SQL Server 2005.

    How many times have you issued a query that you had no idea how long it would run, just to loose your patience after 10 minutes and rolled it back, forgetting that it'll probably take 10+ minutes to rollback anyway?

    Come on don't be shy... raise your hand... I know you're one of them :)

     

    Although there is no way to get the rollback time from the ROLLBACK command (if there is do let me know) we can use an extended way of calling the KILL command.

    KILL command has an option WITH STATUSONLY that tells you how long until will the killed process completes.

    We call it like this:

    KILL 60 WITH STATUSONLY;

     

    So basically you can do this in one window:

    SELECT @@SPID as SpidToKill -- remeber this. It was 60 in my case BEGIN TRAN -- our really long query ROLLBACK

    And after you issue a rollback run the following in another window:

    KILL 60 -- 60 was the remembered @@SPID from the above KILL 60 WITH STATUSONLY;

    the message you'll get is this:

    /* spid 60: Transaction rollback in progress. Estimated rollback completion: 2% Estimated time left: 130 seconds. */

     

    And after running it a few more times you can see that the times are nicely displayed:

    /* SPID 60: transaction rollback in progress. Estimated rollback completion: 7%. Estimated time remaining: 92 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 18%. Estimated time remaining: 81 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 29%. Estimated time remaining: 71 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 94%. Estimated time remaining: 3 seconds. -- we get this message after the spid has completed dieing Msg 6106, Level 16, State 1, Line 2 Process ID 60 is not an active process ID. */

    We can see that the progress report is quite handy to have. and if it takes around 10 minutes you at least know you have time for some coffee.

    So don't let those long lasting rollbacks eat your nerves. Happy KILL-ing!

     

    kick it on DotNetKicks.com
     

    posted @ Monday, November 03, 2008 10:04 AM | Feedback (9)

    Tuesday, October 21, 2008

    Different ways how to escape an XML string in C#

    XML encoding is necessary if you have to save XML text in an XML document. If you don't escape special chars the XML to insert will become a part of the original XML DOM and not a value of a node.

    Escaping the XML means basically replacing 5 chars with new values.

    These replacements are:

    < -> &lt;
    > -> &gt;
    " -> &quot;
    ' -> &apos;
    & -> &amp;

     

    Here are 4 ways you can encode XML in C#:

    1. string.Replace() 5 times

    This is ugly but it works. Note that Replace("&", "&amp;") has to be the first replace so we don't replace other already escaped &.

    string xml = "<node>it's my \"node\" & i like it<node>"; encodedXml = xml.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;"); // RESULT: &lt;node&gt;it&apos;s my &quot;node&quot; &amp; i like it&lt;node&gt;

     

    2. System.Web.HttpUtility.HtmlEncode()

    Used for encoding HTML, but HTML is a form of XML so we can use that too. Mostly used in ASP.NET apps. Note that HtmlEncode does NOT encode apostrophes ( ' ).

    string xml = "<node>it's my \"node\" & i like it<node>"; string encodedXml = HttpUtility.HtmlEncode(xml); // RESULT: &lt;node&gt;it's my &quot;node&quot; &amp; i like it&lt;node&gt;

     

    3. System.Security.SecurityElement.Escape()

    In Windows Forms or Console apps I use this method. If nothing else it saves me including the System.Web reference in my projects and it encodes all 5 chars.

    string xml = "<node>it's my \"node\" & i like it<node>"; string encodedXml = System.Security.SecurityElement.Escape(xml); // RESULT: &lt;node&gt;it&apos;s my &quot;node&quot; &amp; i like it&lt;node&gt;

     

    4. System.Xml.XmlTextWriter

    Using XmlTextWriter you don't have to worry about escaping anything since it escapes the chars where needed. For example in the attributes it doesn't escape apostrophes, while in node values it doesn't escape apostrophes and qoutes.

    string xml = "<node>it's my \"node\" & i like it<node>"; using (XmlTextWriter xtw = new XmlTextWriter(@"c:\xmlTest.xml", Encoding.Unicode)) { xtw.WriteStartElement("xmlEncodeTest"); xtw.WriteAttributeString("testAttribute", xml); xtw.WriteString(xml); xtw.WriteEndElement(); } // RESULT: /* <xmlEncodeTest testAttribute="&lt;node&gt;it's my &quot;node&quot; &amp; i like it&lt;node&gt;"> &lt;node&gt;it's my "node" &amp; i like it&lt;node&gt; </xmlEncodeTest> */

     

    Each of the four ways is different, so use each one where you fell appropriate. You can't go wrong with SecurityElement though. :)

     

    kick it on DotNetKicks.com
     

    posted @ Tuesday, October 21, 2008 10:43 AM | Feedback (9)

    Thursday, October 16, 2008

    Custom user configurable SQL Server Profiler events

    SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.

    But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:

    ProfilerConfigurableEvents

    We can have up to 10 custom events.

    Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)

    A simple code example to demonstrate this:

    USE AdventureWorks GO CREATE PROCEDURE spDoStuff @AddressID int AS UPDATE Person.Address SET ModifiedDate = GETDATE() WHERE AddressID = @AddressID IF @@ROWCOUNT = 0 BEGIN DECLARE @msg NVARCHAR(128) SELECT @msg = N'No data updated. Rowcount 0. AddressId = ' + CAST(@AddressID AS NVARCHAR(10)) EXEC sp_trace_generateevent 82, @msg END SELECT * FROM Person.Address WHERE AddressID = @AddressID GO -- UPDATES OK EXEC spDoStuff 1 GO -- this id doesn't exist. trace event is executed as shown in the below picture EXEC spDoStuff -22 GO DROP PROCEDURE spDoStuff

    ProfilerCutomEvents

    The UserConfigurable:0 event was successfully fired as is shown in the profiler trace.

     

    These custom events are highly underused and there isn't even much talk about them at all. That is a shame because they can be a very helpful addition to your debugging or general execution tracing. By using them you can easily narrow the profiler trace and with that the load on the server over long periods of time making them ideal for finding seldom occurring problems in production environment.

     

    There is one downside to them though. Running sp_trace_generateevent requires ALTER TRACE permissions to run. Most likely the user running the stored procedure doesn't have that permission. Unless you're running under SA of course :)

    A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it.

     

    kick it on DotNetKicks.com
     

    posted @ Thursday, October 16, 2008 10:59 AM | Feedback (2)

    Powered by: