Thursday, May 08, 2008
After a long while I've finally managed to create a release version. I've also added some cool new features.
It is completely free and has no expiration date like the prior beta versions.
I've also changed the RSS feed to Feedburner to which you can subscribe to at: http://feeds.feedburner.com/SsmsToolsPack
If you're subscribed to the old feed please change it.
In SSMS Tools Pack 1.0 you can find these features:
- Uppercase/Lowercase keywords:
Set all keywords to uppercase or lowercase letters. Custom keywords can be added.
- Run one script on multiple databases:
Run selected or full window text on selected databases on the currently connected server.
- Copy execution plan bitmaps to clipboard:
Copy selected or all execution plans to a bitmap that is saved on the clipboard.
- Search Results in Grid Mode and Execution Plans:
Find all occurrences of your search string in the execution plans or in the results in datagrid mode.
- Generate Insert statements for a single table, the whole database or current resultsets in grids:
Generate insert statement from your data.
- Query Execution History (Soft Source Control):
Save all executed queries to file or database and easily find them.
- Text document Regions and Debug sections:
Add Regions and Debug section in your scripts to ease development experience.
- Running custom scripts from Object explorer's Context menu:
Speedy execution of custom scripts from Object Explorer's context menus.
- CRUD (Create, Read, Update, Delete) stored procedure generation:
Generate Customizable CRUD stored procedures for all tables in your database.
- New query template:
Create a template that is shown when creating a new query window.
Currently supported SQL Server Management Studio versions are:
SQL Server Management Studio 2008 CTP
SQL Server Management Studio 2005
SQL Server Management Studio 2005 Express
Hope you enjoy it!
And if you're feeling extra generous there's always PayPal. :)
Sunday, May 04, 2008
Just as the title says :)
You can read the article here.
Thanx to Kevin Kline for publishing it!
Tuesday, April 29, 2008
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
Monday, February 25, 2008
It does when it feels like it, but not in the way you immediately think of.
A few days ago Jeff posted about short-circuiting wondering how it works and since I've played with this a long while ago I thought I'd share my results. Hopefully it will make it clearer how SQL Server behaves when evaluating conditions in its WHERE clause. So let's see how it's done in greater detail.
First we must create our test table with some sample data which will be defined like this:
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
CREATE TABLE t1(id INT PRIMARY KEY, val VARCHAR(10))
INSERT INTO t1
SELECT 1, '19000101' UNION ALL
SELECT 2, 'val 2' UNION ALL
SELECT 3, '20080303'
Now let's play a bit. :)
AND Conditions
Run this statement:
SELECT * FROM t1
WHERE id / 0 = 1
AND id = 2
AND CONVERT(DATETIME, val) > GETDATE()
You'll get this error:
Msg 241, Level 16, State 1, Line 10
Conversion failed when converting datetime from character string.
The execution plan shows us how SQL Server parameterizes our select statement:
SELECT * FROM [t1] WHERE [id]/@1=@2 AND [id]=@3 AND CONVERT([datetime],[val],0) > getdate()
The most obvious condition to fail is of course the divide by zero. You'd think it would be the first to evaluate since it's obviously an illegal call and everything else would be discarded. However because of the statement parameterization this doesn't happen because at the condition evaluation time the values 0 and 1 aren't known. For SQL Server they are still valid parameters whose condition cost must be evaluated. Going back to our error message we can see that the only row with an invalid date is the one where id = 2. This tells us that query processor first looked up the row by id, then applied the datetime conversion. Thus we get our order. Following query only strengthens our deduction since now we get the "Divide by zero error encountered" because the date successfully converted.
SELECT * FROM t1
WHERE id / 0 = 1
AND id = 3
AND CONVERT(DATETIME, val) > GETDATE()
This example shows us how SQL Server short-circuits conditions based on some internal threshold that measures condition importance and their evaluation order. There's nothing we can do about this and the order of condition evaluation when actually reading data is unknown to us.
OR Conditions
Run this statement:
SELECT * FROM t1
WHERE id = 3
OR id / 0 = 1
Here it is a whole different story than above with only AND conditions. Here you immediately see the error "Divide by zero error encountered". If you take a closer look you'll see that we never get to the execution plan phase. Why does this differ so much from the AND conditions? Because OR's immediately mean Table/Index scan. Therefore no parameterization is done and at the query evaluation time all of the values are known. This is when the divide by zero is caught before the execution plan is even built or taken from cache and our query fails. It doesn't matter that our first condition is already true.
Literal Conditions
Run these statements:
SELECT 1 WHERE (1 = 1) OR (1 / 0 = 0)
SELECT 1 WHERE (1 = 0) OR (1 / 0 = 0)
We see that with literal conditions the short circuiting is being done on 1=1 and 1=0 and the 1 / 0 isn't evaluated at all, but this is not to be mistaken to work identically with real data as is shown above.
CASE Statement Conditions
Run these 2 statements:
SELECT * FROM t1
WHERE CASE
WHEN ISDATE(val) = 0 THEN 0
WHEN CONVERT(DATETIME, val) > GETDATE() THEN 1
ELSE 0
END = 1
SELECT * FROM t1
WHERE CASE
WHEN CONVERT(DATETIME, val) > GETDATE() THEN 1
WHEN ISDATE(val) = 0 THEN 0
ELSE 0
END = 1
These 2 statements nicely illustrate that the order of condition evaluation in the CASE statement is always from first to last. The first statement will return one row since the second rows with invalid date will be filtered out by the ISDATE() function, while in the second statement it won't be filtered out and it will throw an error. This is of course quite logical because a CASE statement is simply a big if... else if... else... statement written differently.
If C++/C#/VB has short-circuiting why can't SQL Server have it?
To truly answer this let's take a look at how both work with conditions. C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false.
We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.
Saturday, February 16, 2008
We all heard about the Chuck Norris Facts, right?
Well if you're a fan of security you have to be familiar with Bruce Schneier and his excellent blog.
What I didn't know is that he also has a collection of facts!
Just how cool is that???
Here are some I found funny:
- For Bruce Schneier, SHA-1 is merely a compression algorithm.
- Bruce Schneier once compressed a single bit of information to half its size.
- Bruce Schneier doesn't need backups because his hard drive knows that failure is not an option.
- Bruce Schneier shaves with Occam's razor.
- Bruce Schneier knows the last digit of pi. He won't say it because it's considered a matter of national security.
- Setting SSID of an open Wi-Fi network to "bruceschneier" makes it completely secure.
- Santa Clause doesn't know if Bruce Schneier has been good or bad
- When Bruce Schneier observes a quantum particle, it remains in the same state until he has finished observing it.
- When you email Bruce Schneier, you don't need to press "send".
- Deep Blue couldn't beat Kasparov until Schneier pwned it.
Read others here.
Monday, February 11, 2008
This is an implementation of the Grayscale Immersion Watershed Segmentation in C# based on the
Vincent-Soille immersion watershed algorithm
Read Full Post.
Friday, February 08, 2008
I've written an article here on SQL Team about Integrating Profiler and PerfMon Log Files.
Troubleshooting SQL Server requires the use of both PerfMon (Performance/System Monitor) and SQL Trace files created by SQL Profiler or directly by SQL Server trace. Analysis of the gathered data is much easier if you can correlate your trace file with the PerfMon counters. In this article I'll show how to create a PerfMon counters log file and SQL Profiler Trace file, how to read them both and how to correlate the two files in SQL Profiler.
Integrating Profiler and PerfMon Log Files
Monday, February 04, 2008
Probably everyone is familiar with the Count(*) function in SQL Server.
But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.
Let us banish the confusion back to the dark realms where in belongs to:
DECLARE @t TABLE (val INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 5
SELECT COUNT(*) AS CountAll, -- counts all rows
COUNT(val) AS CountAllNoNull, -- counts rows that don't contain NULL
COUNT(DISTINCT val) AS CountDistinctNoNulls, -- counts the number of distinct values
COUNT(*) - COUNT(val) AS CountOfNullValues -- count of NULL values in the column
FROM @t
There are 3 ways in which Count() works:
COUNT(*) - Counts all rows in the table
COUNT(ColumnName) - Counts all rows in the table that don't contain NULL in the specified column
COUNT(DISTINCT ColumnName) - Counts all DISTINCT rows in the table that don't contain NULL in the specified column
We can see that with the use of these 3 options we can get easily get the number of NULL values in the column.
So where does the Sum come in here? Sum can be used to get a count of practically anything with just one table scan.
Let's illustrate:
SELECT SUM(CASE WHEN val < 3 THEN 1 ELSE 0 END) AS LessThanTree,
SUM(CASE WHEN val > 4 THEN 1 ELSE 0 END) AS MoreThanFour
-- SUM(CASE WHEN <Any condition you can think of> THEN 1 ELSE 0 END) AS Col1
FROM @t
To follow the primary school logic here:
If something fits the condition return 1 else return 0. The sum of all 1's returns the number of items that satisfy the desired condition.
And the condition is limited only by your business requirement.
You see there's no need to write nested select statements similar to this:
SELECT (SELECT COUNT(*) FROM MyTable WHERE <someCondition1>) AS cnt1,
(SELECT COUNT(*) FROM MyTable WHERE <someCondition1>) AS cnt2
Jeff has also has a good post about translating these kind of queries to case statements here.
And this might even provide a nice interview question...
Tuesday, January 08, 2008
I've written an article here on SQL Team about Application Locks in SQL Server 2005
Application locks aren't a well known area of locking in SQL Server, but they can be very useful for special scenarios. They work in a analogous way to the lock() construct in .Net and are basicaly user defined mutexes in SQL Server.
It's a second part in the locking series articles:
Part 2: Application Locks (or Mutexes) in SQL Server 2005
Part 1: Introduction to locking in SQL Server 2005
Saturday, January 05, 2008
This is a very "serious" talk presented by legendary Israeli investor and community-builder Yossi Vardi.
It talks about the dangers of blogging especially for males.
Direct video link here.
Worth the watch!
Thursday, January 03, 2008
In 2007 I've worked a lot with NUnitForms. It contains all major controls testers and you can easily create
your own ones for e.g. a Data Grid or a 3rd party control. I had to create one for Janus GridEx and it wasn't really
that hard. A little reflection went a long way :) Lately, the whole project was at a standstill for some time but now it seems
it's revitalizing nicely with new people on board. So if you haven't yet go take look. This is just one open source GUI testing platform
and I'm sure there are others... I haven't checked them out though.
Now to the big question: Is it worth it? It depends...
I'm not a hardcore TDD practitioner but I like having repeatable unit tests. But having automated GUI unit tests is in my opinion a waste of time.
Why? It simply isn't worth spending time writing them. You should have enough code coverage with non GUI tests to find any bugs long before
hitting the GUI. You might want to be tempted to unit test control positions or visibility or some other property... don't bother.
You'll spend your time better if you write more non GUI unit tests and check your GUI by hand (eye).
So what are they useful for? Well... I've used them for acceptance and integration testing with great success.
Integration Tests...
... test how different parts of your application work together. These kinds of GUI tests proved to be VERY useful and they are worth investing time in.
They have a great ROI (return on investment) once they're written.
For example we had a large workflow based application that was completely async in nature. It had a windows forms client that could be deployed on
multiple computers, all those clients connected to a single server that hosted a few webservices for data modifications, retrieval and some other stuff.
The whole app was also connected to Sharepoint server, Exchange Server, SQL Server, used InfoPath and a 3rd party software to export data from
and into it. With one single GUI integration test we could easily see if everything worked together as it should. We mocked the InfoPath and
3rd party software but everything else ran great.
The point of using GUI tests here is to see how and if your whole application works with all parts integrated together from GUI interaction
to Exchange connectivity. Even the best non GUI integration tests simply don't do that.
Acceptance Tests...
... test if your software is up to some specs usually defined by the business process. They're in essence a black box test.
You don't care what goes on inside the software you just want to see if the output is correct for a certain input.
In workflow based processes this can be very useful since you know what the input and output parameters are for your workflow.
For example you start the workflow in your app via NUnitForms, pressing "move in workflow" buttons until the workflow end and then check if
the output parameters have the correct values. You can check for various different GUI parameters in this test along the way but as I said above
don't go overboard. I usually just check for visibility of certain panels or grids... simple things like that.
When I first started with GUI testing I thought it was a really big waste of time. And to tell you the truth if you don't draw the line somewhere it is.
I even had a moment when I thought it was a good idea to test everything only with GUI tests. That was so far over line I couldn't even see it anymore.
Luckily that moment passed pretty fast. Don't make the same mistake. If used wisely automated GUI tests are a big benefit.
Wednesday, December 19, 2007
I've written an article here on SQL Team Database Backups in SQL Server. It's a first of two articles on backups and backup strategy
Backing up a database is one of the most important things you need to do when having a database driven application. It 's only all of your data in there, right? But often developers and management don't realize the importance of backups and overall proper backup strategy for the most important side of the business – data and it's consistency.
Part 1: Introduction to SQL Server 2005 Database Backups
Tuesday, December 18, 2007
it's my opinion that today we unfortunately live in a world that requires some kind of harder problem solving skills from maybe 20% of the population. And I'm being very very generous.
So putting that percentage with the question in the title would give an answer similar to: "Because not every one can do it." DOH, right? I'd say you're wrong since everyone had a problem to solve at least once in a lifetime. So what's the problem? Why is it perceived as hard? Let's take a little trip through your past.
When you were just an infant, what was your biggest problem? Getting fed, changed and burped, right? You cried for a bit and your problem was solved by your caretakers. Then you got a little older and you added playtime to the list (hopefully you could do burping by yourself, and potty training was fully successful). You played with your toys in your little world and could do wonders there. Life was simple. Then you started going to school and all hell broke loose. you started puberty and thought everyone else is an idiot. School ended more or less painless and since this is not a blog about gardening or ballet but about technology and your reading it means you probably went to a science oriented university. Unfortunately 95% of primary and secondary schools and universities aren't driven towards problem solving but towards cramming information. After you finish that and come to work to realize you know nothing about anything in the real world. You get thrown into the harsh reality with stuff coming at you from all over the place all the time. Things just get more complex with more and more parts mixing in unbelievable ways.
And herein lies the beauty of engineering. Since the dawn of time engineering has been about solving problems and not everyone thinks like an engineer. If you remember what you've learned when playing with your Lego's :) or at school at a class called Systems Theory or something similar sounding (if you paid attention and wasn't too tired from previous nights activities :)) you can see the whole concept of problem solving in basically all areas of life comes down to one simple methodology:
Modular Design also know as Black Box Design.
Every single good technical principle today in every single piece of technology that has to work well relies on that simple methodology. Remember this sentence for the rest of your life:
Solutions to big problems are built from solutions to smaller problems.
To do this well you have to have two very important skill sets:
- An ability of Visualization
- A good understanding of the tools you work with
An ability of Visualization
Visualization is necessary because without it you simply can't split your one big problem into smaller parts. Good sense of visualization leads to better sense of abstraction. Abstraction simply means lessening the problem scope. So visualization and abstraction are tied into a continuous circular reference. You visualize a solution and split it into pieces/modules/black boxes. You essentially abstract it into smaller parts.
Like this Renault Clio V6 engine on the left. Smaller parts or modules only have to perform a single task. A single unit of work. If they don't, abstract them further. This gives the ability of module reuse which lessens costs and increases robustness which in turn increases fault tolerance and the lifetime of the final product. To achieve all this a single black box/module has to be deterministic. The output must always be the same for the same input. In other words the inner state of the module is time independent.
With visualization and abstraction comes a sense of beauty. Beauty is one of the the best ways to evaluate how good a solution is. A good solution is pleasing to the senses. It's symmetrical. It simply looks good. And of course it also works good. It's a simple law of nature. Good and beautiful things work better and thus prevail.
A good ability of Visualization means you can design things that will work well, look good and generally be optimal.
A redesign process when you abstract more, keeping the functionality is called refactoring. Consequently refactored solutions are generally better since they are more beautiful which is one of the best indicators of quality.
A good understanding of the tools you work with
Understanding your tools at disposal gives you the ability to develop a solution better, faster and more elegant. You have to know the abilities and limits of your tools. You must also be open to possibilities of new tools doing a better job than the old ones. Don't be afraid of the change. Change is good. But also don't try to solve everything just with a hammer or just with a needle. Combine them.
Pretty picture for easier visualization of abstraction of visualization of abstraction of ....
Let's not kid ourselves. Solving problems isn't a piece of cake. It takes time, patience, care for details, etc... So don't be overly bothered if you don't succeed on the first try. Practice makes perfect and we always have to learn new things.
But the first thing you have to do is:
KNOW AND DEFINE THE REAL PROBLEM!
Wednesday, December 12, 2007
I've written an article here on SQL Team about locking in SQL Server.
It's an introductory article that shows lock modes, lock granularity and lock compatibility matrix
and it will be followed by a few more advanced ones on the topic of locking.
Part 1: Introduction to locking in SQL Server 2005
Friday, December 07, 2007
I've released a new version of the add-in.
Most of the reported bugs are fixed and I've added two new main functionalities:
- Search Results in Grid Mode and through Execution Plans
- Generate Insert statements for a single table, the whole database or current resultsets (yes all of them) in grids
Insert statement support scripting binary data up to 5 Mb from image and varbinary(max) columns
Minor changes include customizable shortcuts and the ability to move the menu between tools submenu and main menu bar.
You can find it on www.SsmsToolsPack.com or subscribe to the RSS feed to get notified of the new changes
