The default setting is the wrong setting for SQL Server. However, unless this has caused you a problem or you are thorough to point of OCD, this may not be set on your server.
Unless you are fighting a memory bottleneck, it probably won’t affect you too much but it is hard to give SQL too much memory.
The setting is “Maximize Data Throughput for Network Applications” and on by default. It sounds like a good thing. To the contrary, here is documentation from MSDN.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
Maximize Data Throughput for Network ApplicationsTo optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.To check the current setting on your operating system1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Maximize Data Throughput for Network Applications
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.
To check the current setting on your operating system
1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Happy Tweaking.
Window Server 2008 R2 Server Core, that is. When this product went public beta yesterday, I scanned the feature list to see if were any new and cool features for the DBA. Nothing specific as far as I could see. It is x64 only and does support live migration which is equivalent to vmotion on VMWare. Cool but neither are necessarily SQL related.
In case you have not heard about Server Core. It is a stripped down version of Windows Server with no GUI. You basically log into a cmd prompt or administer remotely.
I just read that the .Net Framework is installed on Windows 2008 Server R2 Server Core Edition at Andrew Fryer’s Blog. This means SQL Server can be installed.
To quote Andrew, here are the benefits of SQL Server on Server Core :
There is just one problem. Is it still Windows if there are no… umm… Windows?
More details on Windows Server 2008 r2 can be found in the reviewers guide.
They drew the line in the sand at the OS.
First and foremost, 32-bit is done. History. Archives. Windows Server 2008 R2 is the first Windows OS platform to go 64-bit only, and frankly it was high time. Customers have been unable to purchase a 32-bit server CPU for over two years now, and the advancements in CPU architectures really dictated that we squeeze as much performance out of customers’ hardware purchases as possible. The move to 64-bit is a first step.
Read the full post here.
I assume they *could* still support SQL11 running in wow64. We will see. I gave my opinion, as did others, in this post a while back.
The PHP Driver for SQL Server 2005 RTM'ed today. A few months ago, I posted about the WIMP(Windows 2008, IIS7, MSSQL, PHP) stack that Microsoft was working on to take on the LAMP(Linux, Apache, MySQL, PHP) stack. Supposedly, Microsoft has made some tremendous strides in PHP performance with IIS7. I was like "meh" until today when they released the PHP driver for SQL Server 2005.
Test drive time?
Hmm, maybe it is time to setup a wordpress mirror. While looking looking for some IIS\PHP benchmark, I found this post that details the setup. I am pretty sure I could hack the MySQL code to work on MSSQL. *Looks at plate. Reconsiders.*
This post is useless without numbers
What does concerns me a little is the lack of benchmarks. It seems like it would be really easy to take the same open source php apps and the same hardware then run some linux vs. windows 2008 performance tests. I just did some quick searches so they may exists. The windows performance team did release their php tuning guidelines. Come on with the numbers Microsoft!
Time to over deliver
Really, it has to be better than LAMP or the community will chew it up and spit it out. I hope WIMP is actually hefty, hefty, hefty. I think MSSQL(and Oracle\DB2) have had slower growth due to open source RDMS bleed. A stout windows PHP platform, this driver and SQL Server 2008 Web Edition will better position MS in that segment.
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
The full article can be found here.
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.
Zain announced that there will be a Second Life Virtual Launch April 26th. It looks like the sessions are the same as the live events. That means I can mix and match VS and Windows 2008 sessions since I have already attended the SQL track in Dallas. Here are the details from Zain.
If you have not had a chance to mess around with SQL Server 2008 much yet, I highly recommend you checking it out. They are not 400 level sessions but they are not marketing fluff either.
TechCrunch reports that google is going to be entering the cloud data services market. Actually, they are going to make available to the public what is already running production google apps. This is after Microsoft announced their public free beta of SSDS and Amazon launched simpledb.
What does this mean for the DBA? That is hard to say. I find it hard to believe a round trip over SSL to a remote data center will provide the performance needed for enterprise apps. I don't know enough about it to speculate yet. Denis Gobo posted about this topic when amazon launched. Check it out. Good info.
We regret to inform you of a passing in the SQL community. Read more here.
Rick Heiges has a post on my favorite new feature in SQL Server 2008, filtered indexes.
So does Decipherinfosys along with a good description on the difference of indexes and statistics if you need some background.
SQLBlogcasts has gotten an upgrade and Tony posts some great stats. Congrats!
Not SQL per say but the Hyper-V release candidate has been released. Speaking of Hyper-V, Sriram posts his slide decks on virtualizing SQL. Part 1 and Part 2.
This is a great starting point for SQL Server 2008 as is this. These come by way of the MSDN\Technet update blog.
The SQL Server 2005 sp3 debate continues.
The SQL ISV team posts a performance improving cursor rewrite sample. However, it is not ANSI compliant :) which is odd since most ISV tsql code needs to be portable.
Paul Nielson will be releasing a DVD.
While we are at it, check out the new SQL Server social network.
alt head: Got ADD?
Technorati Tags: SQL Server,SQL Server 2008,SQL Server 2005,SQL Server 2000,tsql,cursors,virtualization,filtered indexes,social networking
WIMP. Not Weakly Interacting Massive Particles of dark matter fame. Windows, IIS7, MSSQL, PHP; Microsoft is going after the LAMP markets. I suspect they have a pretty good chance of converting quite a few customers. This means DBA's should be prepared. Hopefully, it will be better to support than JAVA and SQL Server :)
There are substantial improvements in IIS7(IIS6 with an addon) for PHP support through the Fast CGI open protocol and other enhancements through the Zend partnership.
Microsoft has also released the PHP Driver for SQL Server CTP. More info can be found on the team blog.
I don't have much more info on the PHP driver but I am going to keep up with it. There is a time and place for everything. If MS delivers as good or better than LAMP, why reinvent the wheel in .net when an enterprise ready production proven open source app is the right answer. For example, Word Press. Now there are a million variables that go into that decision but having options in your toolbox can't be a bad thing.
ALT HEAD: Honey, turn off the LAMP.
The big news of the week was the HH{H} launch. A lot of marketing fluff. Not just from MS but from vendors trying to ride the wave. That said, I think the rest of the year is going to be really exciting technology wise.
There are some good webcasts at the virtual event site through the slick but slightly buggy silver light interface. Check it out.
The MS SQL site got a facelift. I suspect it will replace the original site soon. Props to ThePremiers for breaking it.
Dan reports the return of the tsql debugger, object search and the new activity monitor. He demo'd these features and the other manageability features at the launch event.
SQL Server 2008 is setting records. Most impressively in loading 1TB+ in 30 minutes with SSIS. Read it on the SQLPerf site here and here, the SQLCAT site, Erin's blog, and Denis's blog.
Peter has the scoop about issues with SQL Server 2008 and leap year. Whoops :) Good thing they didn't launch today.
Linchi comments on the intel hexacore procs that should hit Q3-4.
Derek C drops knowledge on the SQL Server 2008 performance data collector.
This might be HAWTALTA to some but I consistently here people complaining about the speed of SQLWB. Jeff posts links to resources.
Bonnie posts the 2008 version of samples on codeplex. Direct dl here. Details here.
Check out this very detailed post on SNAC for the horse's mouth.
Andy has a no nonsense post on sparse columns.
I should have mentioned this last week. Simon did a great job in detailing full text in SQL Server 2008 in a series of posts. Start here.
Have a great weekend!
Full story here.
"Technology alone is not the answer," Brokaw said. "It will do little good to wire the world if we short-circuit our conscience."
There are alot of changes in clustering on Windows 2008. As a matter of fact, you no longer have to have a quorum drive. While there are a lot of changes and improvements, the focus of this release is ease of use. I am going to start here. Let me know if you run across some good resources.
The big news of the week is the release of CTP6. There millions of posts about it but let me point you to some interesting ones. Dennis Gobo points out the new DMV's. SSQA reports on the features that have been removed like SSNS. Personally, I think the highlight of the CTP is filtered indexes. I will be posting a blog about them this weekend.
Aaron points out that cumulative update 6 for SQL Server 2005 sp2 is available.
Uhoh, SQL Server driver for PHP CTP has been released. Get your LASP on. Haha. Joking aside. I went to a Windows 2008 event this week and MS is gunning for Apache. Hopefully, SQL powered PHP apps are better to support than SQL powered Java apps.
How nerdy are you? If you answered, very. Hop on over to the Second Life Technet event this Saturday. Details here.
Laurentiu Cristofor posts a FAQ on SQL Server password policies. He has some good info on his blog. Check it out.
Not new but relevant to DBA's, check out this Windows 2008 performance white paper.
Apparently, RTM is actually sp1 and the first sp will be sp2. Confused? Check out this post.
The big news of the week is Windows Server 2008 RTM'ing. Read more here.
The storage engine team has begin a series on bulk insert. It is definitely recommended reading. Part 1, Part 2, Part 3.
The SQL Server development customer advisory team has launched http://SQLCAT.com. They have some nice content.
There is some interesting commentary on obtaining certs on SQL here.
Simon S. releases a new version of the spatial viewer on codeplex. Get yer spatial on!
Edit:
This one just missed post but it looks cool so I will add it in,
Danny has just released a new SSMS plug in that can color code your connections. That way there are no more OMG moments.
Windows Server 2008, that is.
"Windows Server 2008 (and Vista Service Pack 1) are slated to release to manufacturing (RTM) on Feb. 6, according to several sources. That gives the company plenty of time to churn out disks for distribution at the event, which Microsoft executives have characterised as the company's "biggest enterprise launch ever."The early February RTM means that the long-awaited server operating system will be available for the big event. Visual Studio 2008 is already out. Microsoft last week said SQL Server 2008 has slipped into the third quarter. Previously, the company said the database would be available in the second quarter. Microsoft has also promised to deliver its new database release in 36 to 48 months going forward. SQL Server 2003 shipped in November of that year."
Windows Server 2008 (and Vista Service Pack 1) are slated to release to manufacturing (RTM) on Feb. 6, according to several sources. That gives the company plenty of time to churn out disks for distribution at the event, which Microsoft executives have characterised as the company's "biggest enterprise launch ever."
The early February RTM means that the long-awaited server operating system will be available for the big event. Visual Studio 2008 is already out. Microsoft last week said SQL Server 2008 has slipped into the third quarter. Previously, the company said the database would be available in the second quarter. Microsoft has also promised to deliver its new database release in 36 to 48 months going forward. SQL Server 2003 shipped in November of that year."
The full story can be found here.
Read more
HHH Comics RSS feed
R.I.P. Kenh1234
Most regional lauch events are accepting RSVP's now. I suggest you sign up with the quickness.
http://www.microsoft.com/heroeshappenhere/register/default.mspx
Have you every had a query that should totally be using an existing index and doing a seek but it keeps doing a clustered index scan? In SQL Server 2008, you can force it to do a seek. That may or may not be a good thing. Let's take a look at an AdventureWorks example.
--logical reads 1246
select ProductID, OrderQty from sales.SalesOrderDetail
where ProductID > 776
This query generates this plan.
Now let's add the hint.
--logical reads 271264
select ProductID, OrderQty from sales.SalesOrderDetail with (forceseek)
This query generates this plan. The second query plan contains the index seek but logical reads are 20 times more. This is because it has to do lookup for OrderQty. Obviously using the hint would be a bad thing. So when would be a good time to use this hint? Let's look at this query create proc p1 @p int as select c1, c2 from t1 where c3 = @p t1 has 1 million rows 99% of c3 =1 .5% of c3 = 2 .5% of c3 = 3 99% of the queries run with @p = 2 or 3 The problem is if the query compiles with c3 = 1. The optimizer will choose a CI scan. In SQL 2005, we could recompile each time at the statement level or we could use an optimize for hint. Now we have another tool in the bag of tricks.Use with caution.
Half of this post is to show off the new multi server query feature and the other half is to show off the new web 2.0 content with the help of the Live Content module from our friends over at http://mandeeps.com . So if you check out this video, you can see how useful this could be. Let's say you have all of your servers registered in SSMS 2008. Here are a few possibilities that come to mind.