Microsoft’s announced future direction is for SQL Server to become a “data platform” encompassing not only traditionally structured database data, but unstructured information such as text documents, xml documents, spreadsheets, streaming media, etc. Furthermore, they foresee that sooner rather than later, data management and storage will be a web service enshrouded in the Cloud, which has become a techno-chic synonym for the internet. The idea is that you don’t need to know where the data is stored or where your database management system is located. You just request data from a url somewhere (probably China where the technician work very cheap). When you need it, it appears out of the cloud.
I have my doubts that this will find quick acceptance. The internet is by nature unreliable and insecure. To date, the hackers are staying comfortably ahead of the hacker police.
I could be wrong and often am. No doubt such a scheme could be a great deal less expensive than maintaining your own database infrastructure. I am curious to know how many of you would delegate the safety and accessibility of your mission-critical data to the Cloud? If you have a minute, could you email me your thoughts on this?
ksurvance@sql-consulting.com
______________________
Tuesday, February 10, 2009
Sunday, January 11, 2009
TOP and ORDER BY operators in SQL 2005/8
Changes to the TSQL language beginning with SQL Server 2005 can change the order of results returned from a query selecting from a view. That in turn, has the potential to break some application functionality. Most sites will be unaffected, but some may find that certain code is broken. If your application depends on sorting results from a view defined as:
SELECT TOP 100 PERCENT…
…
…ORDER BY THISCOLUMN
You might be affected by this change.
Simply put, a view created with an ORDER BY clause is no longer guaranteed to return rows in the specified order. In order to guarantee the order of the result set In versions newer than SQL Server 2000 you must sort the result set in the query that selects from the view, not in the view definition.
If you are planning an upgrade from SQL 2000, you certainly want to be aware of this so you can test your existing code. If you have already upgraded you may want to make sure that subtle changes in application functionality have not gone unnoticed.
Views that are created with a TOP operator and an ORDER BY clause are still guaranteed to return the same rows, but the rows are not guaranteed to by in the sequence defined by the ORDER BY clause. In most cases the order will be correct, but not in all cases.
This is not a bug, it is a deliberate change made for the purpose of bringing TSQL into closer compliance with the ANSI SQL language standard. Views were never intended to return sorted results. But, because it was possible in previous versions of SQL Server, there is a significant amount of code in productions systems that will be affected by this change. Microsoft has a hotfix for later SQL versions which restores default SQL 2000 behavior. They describe it as a temporary fix designed to give you time to rewrite any code that is broken, so it is not likely to be around for very long.
If you are interested in the technical details of why this was made, I recommend looking at this article by Itzak Ben Gann in SQL Server magazine, January 2009:
http://www.sqlmag.com/Articles/ArticleID/100697/100697.html
SELECT TOP 100 PERCENT…
…
…ORDER BY THISCOLUMN
You might be affected by this change.
Simply put, a view created with an ORDER BY clause is no longer guaranteed to return rows in the specified order. In order to guarantee the order of the result set In versions newer than SQL Server 2000 you must sort the result set in the query that selects from the view, not in the view definition.
If you are planning an upgrade from SQL 2000, you certainly want to be aware of this so you can test your existing code. If you have already upgraded you may want to make sure that subtle changes in application functionality have not gone unnoticed.
Views that are created with a TOP operator and an ORDER BY clause are still guaranteed to return the same rows, but the rows are not guaranteed to by in the sequence defined by the ORDER BY clause. In most cases the order will be correct, but not in all cases.
This is not a bug, it is a deliberate change made for the purpose of bringing TSQL into closer compliance with the ANSI SQL language standard. Views were never intended to return sorted results. But, because it was possible in previous versions of SQL Server, there is a significant amount of code in productions systems that will be affected by this change. Microsoft has a hotfix for later SQL versions which restores default SQL 2000 behavior. They describe it as a temporary fix designed to give you time to rewrite any code that is broken, so it is not likely to be around for very long.
If you are interested in the technical details of why this was made, I recommend looking at this article by Itzak Ben Gann in SQL Server magazine, January 2009:
http://www.sqlmag.com/Articles/ArticleID/100697/100697.html
Tuesday, December 23, 2008
SQL 2008 cluster installer bug
I just got back from an on-site assignment in Denver. W were migrating to SQL 2008 on a new set of Windows 2008 clustered servers and ran into an installer bug. We completed the install of the first node but the install failed for the second node because of an invalid product SKU. The SKU was valid. It was slipstreamed in by Microsoft when the product was purchased and downloaded. It appeared correctly in the SKU input box just before the error. We tried several times with the same result and eventually called Microsoft.
Microsoft is well aware of the bug and has a hotfix which, unfortunately, didn't work for us either. Microsoft and others have also posted workarounds which all involve running setup from the command prompt with a varying number of parameters, depending on who you are talking to. Microsoft's example has an enormous number of parameters which you can find by following links from these pages: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/13a256b0-b95b-46a7-92d5-17a0b763f8dd/
What finally worked for us was very simple: Execute this in the command prompt while logged in as an admin:
setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER"
By the way, the install process for clustered instances has changed significantly. Now the cluster wizard has been broken into two wizards, one creates a single-node cluster (whatever that means). Then you must go back to the setup start screen and run the Add Node wizard for each node you want to add. You must run it from the node you want to add, not the primary node. SQL Server binaries are not moved to the second node until you do that.
Microsoft is well aware of the bug and has a hotfix which, unfortunately, didn't work for us either. Microsoft and others have also posted workarounds which all involve running setup from the command prompt with a varying number of parameters, depending on who you are talking to. Microsoft's example has an enormous number of parameters which you can find by following links from these pages: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/13a256b0-b95b-46a7-92d5-17a0b763f8dd/
What finally worked for us was very simple: Execute this in the command prompt while logged in as an admin:
setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER"
By the way, the install process for clustered instances has changed significantly. Now the cluster wizard has been broken into two wizards, one creates a single-node cluster (whatever that means). Then you must go back to the setup start screen and run the Add Node wizard for each node you want to add. You must run it from the node you want to add, not the primary node. SQL Server binaries are not moved to the second node until you do that.
Friday, December 5, 2008
Amateurism and SQL Server
The Marketing department drives everything at Microsoft, especially product design and development. A while back this group decided that SQL Server was an "administration-free" database. Their premise is that my grandmother should be able to design, construct and manage an enterprise data platform without having to bother about learning much of anything.
For example, the table creation wizard allows a completely unqualified person to actually create a table. It defaults to creating a primary key on an identity column using a clustered index.
This does insure that the table 1) has a PK 2) has a clustered index, and 3) the clustered index isn't going to cause page splitting, even though the table creator has little idea what any of this means. This is a good thing for someone with a small startup that needs a database and can't afford professional help. However, I see it in large corporations done by people who are considered professionals.
I have had discussions with many of them and found that because the wizard's default behavior, They believed that a primary key HAD to be clustered. Also because of the wizard's defaults, they have confused an identity with a real primary key. When I ask them what other column or group of columns is unique in the table, they don't know what I'm asking.
While helpful for an amatuer application, these defaults virtually assure that the database will not scale as a company grows. The placement of the clustered index is critical to good performance. Placing it on an identity in every case is a big mistake.
A nonclustered index will handle an identity lookup as well as a clustered index, so why waste the only clustered index on something that can be done as well by a nonclustered index? Clustered indexes can do certain things much better than nonclustered indexes, like range searches returning many records.
It only makes sense to place the clustered index on a column where it will make a difference. However, deciding which column to place it on requires knowledge and creative thought of which the wizard is not capable and apparently many db designers are not either. It is not that they don't know how to handle the issue: it is that they don't even know there is an issue.
Next post, why an identity is not a primary key.
For example, the table creation wizard allows a completely unqualified person to actually create a table. It defaults to creating a primary key on an identity column using a clustered index.
This does insure that the table 1) has a PK 2) has a clustered index, and 3) the clustered index isn't going to cause page splitting, even though the table creator has little idea what any of this means. This is a good thing for someone with a small startup that needs a database and can't afford professional help. However, I see it in large corporations done by people who are considered professionals.
I have had discussions with many of them and found that because the wizard's default behavior, They believed that a primary key HAD to be clustered. Also because of the wizard's defaults, they have confused an identity with a real primary key. When I ask them what other column or group of columns is unique in the table, they don't know what I'm asking.
While helpful for an amatuer application, these defaults virtually assure that the database will not scale as a company grows. The placement of the clustered index is critical to good performance. Placing it on an identity in every case is a big mistake.
A nonclustered index will handle an identity lookup as well as a clustered index, so why waste the only clustered index on something that can be done as well by a nonclustered index? Clustered indexes can do certain things much better than nonclustered indexes, like range searches returning many records.
It only makes sense to place the clustered index on a column where it will make a difference. However, deciding which column to place it on requires knowledge and creative thought of which the wizard is not capable and apparently many db designers are not either. It is not that they don't know how to handle the issue: it is that they don't even know there is an issue.
Next post, why an identity is not a primary key.
Tuesday, November 25, 2008
Vista rant
If finally bit the bullet and bought a new laptop. I have avoided doing this for years because of the tremendous time investment necessary to load software and configure the system to do the things you need to do. Since I only do this every few years, I generally buy on the cutting edge of technology. It's pretty clear that x64 is the future of PCs so I opted for Vista Business x64 on a loaded Lenovo T61 (4gb, fast drive, etc). I finally have it under control, but it has been a struggle.
There are a lot of 32 bit programs that won't run on Vista x64 and the x64 versions don't exist yet. For example, we use Quickbooks for accounting and I use the Quickbooks timer to record billable time when I am working. I used to use it, apparently. It won't run on Vista at all.
You run into lots of issues like this, especially if you upgrade rather than do a clean install. I am fortunate that mine was a pre-install on the new laptop because I have found in my research that many programs I need and that are supposed to be compatible will only run on a clean install.
If you are thinking about Vista x64, you have to make sure the software you need is available on x64 before you buy.
As a prelude to my rant on Vista itself I want to mention that I am not a knee-jerk Microsoft basher. I make my living supporting a Microsoft product. But I am not an apologist either.
Microsoft has not made Vista an intrinsically more secure operation system. They have merely shifted the responsibility for doing unsafe operations from the OS to the user. Vista was probably designed by their legal department. Every time you start a non-MS program or change any sort of configuration option, you get a pop-up warning; Likewise when you open a website.
Now, I am a geek. I have been using computers since the days of 64k CPM programming. When I can't understand what the message means, how is a casual user supposed to know what to do with the pop-up?
Vista locks your computer down to an unusable degree. If you take all their defaults, you won't be able to do much with your computer.
The item that took the most time to resolve was the change in Remote Desktop. There are a lot of people on the web complaining about it, but I didn't find the answer there. It took trial and error to discover that in many circumstances you must use a full qualified domain name or ip address for the target machine on remote networks over a vpn connection. Just the hostname doesn't work. the vpn opens just fine, but then you cannot see or connect to any machine on the remote network.
Networking in general is a problem. The GUI interfaces and terminology have changed substantially.
Worst of all is the abstraction they have laid over the file system. It requires some effort to see the actual folder hierarchy. I know there are a lot of users out there who don't understand a hierarchical file system. They save documents and can't find them. However, I disapprove of dumbing down the user interface to make it more difficult for an expert user to do what they need to do.
There are a lot of 32 bit programs that won't run on Vista x64 and the x64 versions don't exist yet. For example, we use Quickbooks for accounting and I use the Quickbooks timer to record billable time when I am working. I used to use it, apparently. It won't run on Vista at all.
You run into lots of issues like this, especially if you upgrade rather than do a clean install. I am fortunate that mine was a pre-install on the new laptop because I have found in my research that many programs I need and that are supposed to be compatible will only run on a clean install.
If you are thinking about Vista x64, you have to make sure the software you need is available on x64 before you buy.
As a prelude to my rant on Vista itself I want to mention that I am not a knee-jerk Microsoft basher. I make my living supporting a Microsoft product. But I am not an apologist either.
Microsoft has not made Vista an intrinsically more secure operation system. They have merely shifted the responsibility for doing unsafe operations from the OS to the user. Vista was probably designed by their legal department. Every time you start a non-MS program or change any sort of configuration option, you get a pop-up warning; Likewise when you open a website.
Now, I am a geek. I have been using computers since the days of 64k CPM programming. When I can't understand what the message means, how is a casual user supposed to know what to do with the pop-up?
Vista locks your computer down to an unusable degree. If you take all their defaults, you won't be able to do much with your computer.
The item that took the most time to resolve was the change in Remote Desktop. There are a lot of people on the web complaining about it, but I didn't find the answer there. It took trial and error to discover that in many circumstances you must use a full qualified domain name or ip address for the target machine on remote networks over a vpn connection. Just the hostname doesn't work. the vpn opens just fine, but then you cannot see or connect to any machine on the remote network.
Networking in general is a problem. The GUI interfaces and terminology have changed substantially.
Worst of all is the abstraction they have laid over the file system. It requires some effort to see the actual folder hierarchy. I know there are a lot of users out there who don't understand a hierarchical file system. They save documents and can't find them. However, I disapprove of dumbing down the user interface to make it more difficult for an expert user to do what they need to do.
Thursday, October 30, 2008
Denormalization
I'm convinced that denormalization is a word used mostly by people who don't know how to normalize a schema in the first place. They generally fall into the pose of being a "real-world' dba as opposed to a ridiculous academic or relational purist. Often, the real-world dba's say they have denormalized a database when they have merely failed to normalize it.
Of course, denormalization is something that is often useful and occasionally necessary. However, my belief is that you should first put the schema into 3rd normal form and only then consider denormalization. Until you find a normal form for the schema how can you know whether you have properly understood the process you are trying to model?
Often denormalizers do not understand the long-term implications of what they are doing and build serious long-term performance problems into their design. Typically, you will find that a lot of cursors, colossal union queries, table-valued functions, etc are necessary to patch together the data in a poorly normalized design.
After a database has been in service for months or years it is almost impossible to make substantial schema changes to correct the problems. Often man-years of development have gone into the applications accessing the database and making all the changes that would be necessary is just not an affordable course of action. Developers are reduced to putting band-aids on band-aids to keep their applications chugging along.
Of course, denormalization is something that is often useful and occasionally necessary. However, my belief is that you should first put the schema into 3rd normal form and only then consider denormalization. Until you find a normal form for the schema how can you know whether you have properly understood the process you are trying to model?
Often denormalizers do not understand the long-term implications of what they are doing and build serious long-term performance problems into their design. Typically, you will find that a lot of cursors, colossal union queries, table-valued functions, etc are necessary to patch together the data in a poorly normalized design.
After a database has been in service for months or years it is almost impossible to make substantial schema changes to correct the problems. Often man-years of development have gone into the applications accessing the database and making all the changes that would be necessary is just not an affordable course of action. Developers are reduced to putting band-aids on band-aids to keep their applications chugging along.
Monday, October 13, 2008
More DMV's - Code Performance
I borrowed the original code from a Microsoft engineer who had posted it on the Microsoft website. I would credit him but I forgot who he was and I can't find the post now. At any rate I have added my touches.
This query helps you identify the code that is giving you the biggest problems. It pulls out execution information for every plan in the procedure cache. It aggregates totals and averages for key performance metrics, logical i/o, cpu, etc. It comes with multiple ORDER BY clauses so you can highlight the biggest users of any particular resource. Uncomment the clause you want to use.
The one I use most is logical i/o. I think that is the truest measure of code efficiency. Execution time will vary depending on a lot of factors not directly related to the efficiency of the code and indexing. If the data is in cache, the query will run a lot faster than if it has to page data off the disk. Blocking or a heavy load on the server will also change the execution time of a statement.
Physical i/o is an accident involving how much of the data happens to be in cache when the statement runs. It fluctuates randomly and is not a reliable metric.
But, given the same code, same data, same indexing and the same query plan, logical page reads will always be the same. That gives you a baseline to start from when you are optimizing. You can try indexing tricks, code modification, or forcing different query plans. If logical i/o is reduced, you know you are making progress.
Here is the code. I have throttled it down to the top 100 because a big system can have a huge number of plans in cache and you are only interested in the heavy-hitters anyway. I'll bet you already know that the procdure cache is flushed at each restart of sql server or execution of 'DBCC FREEPROCCACHE' so I won't mention it.
SELECT TOP 100 t.[text] AS [Batch],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Statement]
, qs.[execution_count] AS [Count]
, qs.[total_worker_time] AS [Tot_CPU], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg_CPU]
, qs.[total_physical_reads] AS [Tot_Phys_Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg_Phys_Reads]
, qs.[total_logical_writes] AS [Tot_Logic_Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg_Logic_Writes]
, qs.[total_logical_reads] AS [Tot_Logic_Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg_Logic_Reads]
, qs.[total_clr_time] AS [Tot_CLR_Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg_CLR_Time]
, qs.[total_elapsed_time] AS [Tot_Elapsed_Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg_Elapsed_Time]
, qs.[last_execution_time] AS [Last_Exec], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Tot_CPU] DESC
-- ORDER BY [Tot_Phys_Reads] DESC
-- ORDER BY [Tot_Logic_Writes] DESC
ORDER BY [Tot_Logic_Reads] DESC
-- ORDER BY [Avg_Logic_Reads] DESC
-- ORDER BY [Tot_CLR_Time] DESC
-- ORDER BY [Tot_Elapsed_Time] DESC
-- ORDER BY [Count] DESC
This query helps you identify the code that is giving you the biggest problems. It pulls out execution information for every plan in the procedure cache. It aggregates totals and averages for key performance metrics, logical i/o, cpu, etc. It comes with multiple ORDER BY clauses so you can highlight the biggest users of any particular resource. Uncomment the clause you want to use.
The one I use most is logical i/o. I think that is the truest measure of code efficiency. Execution time will vary depending on a lot of factors not directly related to the efficiency of the code and indexing. If the data is in cache, the query will run a lot faster than if it has to page data off the disk. Blocking or a heavy load on the server will also change the execution time of a statement.
Physical i/o is an accident involving how much of the data happens to be in cache when the statement runs. It fluctuates randomly and is not a reliable metric.
But, given the same code, same data, same indexing and the same query plan, logical page reads will always be the same. That gives you a baseline to start from when you are optimizing. You can try indexing tricks, code modification, or forcing different query plans. If logical i/o is reduced, you know you are making progress.
Here is the code. I have throttled it down to the top 100 because a big system can have a huge number of plans in cache and you are only interested in the heavy-hitters anyway. I'll bet you already know that the procdure cache is flushed at each restart of sql server or execution of 'DBCC FREEPROCCACHE' so I won't mention it.
SELECT TOP 100 t.[text] AS [Batch],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Statement]
, qs.[execution_count] AS [Count]
, qs.[total_worker_time] AS [Tot_CPU], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg_CPU]
, qs.[total_physical_reads] AS [Tot_Phys_Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg_Phys_Reads]
, qs.[total_logical_writes] AS [Tot_Logic_Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg_Logic_Writes]
, qs.[total_logical_reads] AS [Tot_Logic_Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg_Logic_Reads]
, qs.[total_clr_time] AS [Tot_CLR_Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg_CLR_Time]
, qs.[total_elapsed_time] AS [Tot_Elapsed_Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg_Elapsed_Time]
, qs.[last_execution_time] AS [Last_Exec], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Tot_CPU] DESC
-- ORDER BY [Tot_Phys_Reads] DESC
-- ORDER BY [Tot_Logic_Writes] DESC
ORDER BY [Tot_Logic_Reads] DESC
-- ORDER BY [Avg_Logic_Reads] DESC
-- ORDER BY [Tot_CLR_Time] DESC
-- ORDER BY [Tot_Elapsed_Time] DESC
-- ORDER BY [Count] DESC
Subscribe to:
Posts (Atom)
