SQL Server Performance

July 23, 2008

shanetasker — Wed, 23 Jul 2008 17:14:00 GMT

One of the challenges in any team is to ensure that there is effective communication between all team members. The problem is that a team is made up of the sum of the knowledge of all members, but how do you ensure that knowledge is disseminated to all team members? Often there is one person in the team that has specific skills such as the 'Clustering Guy' or the 'Replication Expert'. The problem is that when this person goes on holidays or leaves the organisation the specific skills are lost.

Documentation is often a process that is faced with a lot of resistance; however, most people enjoy pizza, especially when it is free. I was reading an article about a consulting company that had a unique way of dealing with sharing knowledge between their consultants that spent much of the time in the field. Instead of having a ridged structure for sharing knowledge, each team member was expected to perform a 15-minute session each week on something he or she had learnt throughout the week. In return the company would supply a free lunch to all of the consultants. I thought that this was a unique yet effective way to solve the problem of knowledge sharing. Is your team using any unique strategies such as this one to ensure effective communication?

- Peter Ward

July 22, 2008

shanetasker — Tue, 22 Jul 2008 20:30:00 GMT

It does not matter what you do in life, there are always comprises that need to be made. At the moment I am looking to purchase a new house and it is a perfect example of compromise. If money were no object then I am sure that it would be possible to buy the newly renovated waterfront property with all of the mod-cons. However, I need to decide, do I want a waterfront property that needs to be renovated? or do I buy a property several streets back from the water that is already renovated?

When you configure a new server you are often faced with the same dilemma; however, instead of having to choose a location you need to choose between disk, memory, and CPU performance. In an ideal world, each server would have 64GB of RAM, 32 processors, and the fastest storage possible. However, for most organizations budget constraints force a compromise to be made that might reduce the overall performance of the server. For example, rather than purchasing 15K RPM drives you may need to use 10K RPM drives. So do you have any Golden Rules that you use to ensure that the compromises that you make in server configurations ensure the maximum performance?

- Peter Ward

July 21, 2008

shanetasker — Mon, 21 Jul 2008 15:01:00 GMT

A word that I frequently hear when talking to customers is 'consolidation'. When people are talking about consolidation, often they are talking about virtualisation using their virtualisation platform of choice. However, consolidation means to combine multiple source systems into a centralized system. In other words, consolidation is only achieved when the number of systems is reduced and not just the number of physical servers that are being used.

The big issue I currently see with the approach to focus only on the reduction of physical servers is that although hardware and the associated costs such as rack space and power consumption are reduced, the overall management costs in regards to the number of SQL Server instances to manage is not. Real cost savings are achieved when not only the number of physical servers are reduced but also the number of SQL Server instances. What are your thoughts on the current buzz about consolidation?

- Peter Ward

July 18, 2008

shanetasker — Fri, 18 Jul 2008 18:46:00 GMT

Since putting a computer on the desktop of almost everyone in the workforce, there has been a proliferation in the amount of unstructured data that is being generated. From a DBA's perspective unstructured data is any data that does not have a data structure, in other words the data is not stored in a database. A typical example of unstructured data is image and movie files or e-mails and spreadsheets. A recent study by Merrill Lynch found that more than 85 percent of all business information is stored in unstructured data.

The management of unstructured data is one of the biggest problems facing the IT industry. As despite being able to generate the data, it is extremely difficult to provide a mechanism to access this data and derive business value from it. According to Gartner, knowledge workers spend up to 30% of a working week managing unstructured data. Several approaches are currently available to try and assist with this challenge; however, each approach has its own drawbacks. For example, storing unstructured data in a file server or BLOB store adds complexity when developing applications. The complexity is introduced as the application needs to manage the link between the records stored in the database and the system that stores the BLOBs.

Over the last few days I have been exploring the FILESTREAM attribute in SQL Server 2008 and this feature looks like it has finally solved the problem of unstructured data. The FILESTREAM attribute can be applied to a varbinary column allowing SQL Server to store unstructured data for a column on the local NTFS file system. A great article for understating the features in SQL Server 2008 for unstructured data can be found here; however, I would love to hear about how you are currently dealing with this challenge.

- Peter Ward

July 17, 2008

shanetasker — Thu, 17 Jul 2008 18:48:00 GMT

Earlier this week, Microsoft announced that it had acquired the Israeli company Zoomix. Zoomix is a database semantics company that specialises in data cleansing and linguistics and how these can be used to ensure data quality. Although acquisitions are nothing new for Microsoft, this one will directly affect future versions of SQL Server. In the press release, it was announced that Zoomix would become part of the SQL Server data platform to provide customers with a manageable and scalable enterprise-class data quality solution.

One of the current areas of weakness in SQL Server is around data quality. Although there are tools available in SSIS such as Fuzzy Grouping and Fuzzy Lookups, these tools do not compare with the tools available with other DBMS platforms and those supplied by third parties. It will be interesting to see when and how Zoomix will be integrated into SQL Server. I think this acquisition further communicates the fact that Microsoft is serious about ensuring that SQL Server is a complete data platform for all data management needs.

- Peter Ward

Close All Option in SQL Server Business Intelligence Development Studio

dineshasanka — Thu, 10 Jul 2008 13:13:00 GMT

SQL Server Business Intelligence Development Studio (BIDS) is a tool comes with SQL Server installation. If you have open few SSIS packages or SSRS reports in BIDS, there are two options that you can use to close them. First option is Close from which you can close the current package or report. Next option is Close All But This will close all the packages or reports except for the current one.

However, suddenly there was a new option appeared in my PC which Close All. This option can be used to close all the packages or reports.

I found that recently I installed Visual Studio and I believe this option came from the Visual Studio installation.

Free Training

Greg Larsen — Thu, 10 Jul 2008 05:41:00 GMT

Want some free SQL Server training? If so keep your eyes open for SQL Saturday events in your area. SQL Saturday is a mini-summit that uses the multiple presentation and track format. These sessions are presented by industry experts and peers that have a desire to sharing information related to SQL Server.

So far a number different SQL Server Saturday events have already been held, and more are being organized all the time. At the present time SQL Saturday event #5 and #6 are calling for speakers, and open for registration. Go to SQLSaturday.com to find out more about where these events are being held. If one is being held locally in your area and you have a passion for speaking consider submit a session abstract. If speaking isn’t your thing then at least register for the event to reserve your spot since seating at these events are limited.

SQL Server 2008 to be Available in August

bradmcgehee — Wed, 09 Jul 2008 22:02:33 GMT

It's official now. SQL Server 2008 will be available in August, as announced at the Microsoft Worldwide Partner Conference in Houston. See http://www.eweek.com/c/a/Application-Development/Microsoft-to-Deliver-SQL-Server-2008-in-August/ for more information....(read more)

Which Version of SQL Server 2008 Supports Your Favorite New Features?

bradmcgehee — Thu, 26 Jun 2008 02:12:00 GMT

I have been spending a lot of time learning about SQL Server 2008 this past six months. I have attended conferences, on-line training, read articles and blogs, and have spent a lot of time reading SQL Server 2008 Books Online. When a new feature is discussed, like data compression, backup compression, resource governor, partition table parallelism, policy-based managment, data collector, among others, there is generally no reference as to which SQL Server 2008 edition the feature belongs. This is...(read more)

Visual Studio Team System 2008 Database Edition GDR

Greg Larsen — Tue, 10 Jun 2008 04:05:00 GMT

More stuff related to SQL Server 2008. At Tech*Ed 2008 Developer last week they announced the immediate availability of the first public CTP of the Visual Studio Team System 2008 Database Edition GDR. You can read more about it here: http://blogs.msdn.com/gertd/archive/2008/06/03/vsts-2008-database-edition-gdr-june-ctp.aspx

SQL Injection attacks - don't forget to visit guidance information from Microsoft

satya — Sat, 31 May 2008 21:23:00 GMT

One of the biggest threats in IT industry & Database world is unprecedented attacks aka most commonly termed as 'SQL injection'. There is no doubt that biggest database vendors, one of them as Microsoft so far providing recommendations regarding security-related configuration settings since the good & bad times of SQL Server version 2000, not in particular to point DBA or Developer and whole as in Application database, remember ' Slammer Worm '!. Here it is best to refer the...(read more)

3GB Memory & Windows Server 2003 Standard Edition - Interesting question & Valueable insight on resolution

satya — Sat, 31 May 2008 10:46:00 GMT

This is the question: I am configuring a server with Windows Server 2003 Standard Edition and SQL Server 2000 Enterprise Edition. The server has 4GB memory. From what I have read the AWE/PAE switch only works with Advanced Server or Datacenter Editions, which are not an option for me. What is needed for SQL Server to use 3GB of the memory for 2003 SE? Solution: Refer to the forum post - http://sql-server-performance.com/Community/forums/t/14372.aspx in this regard....(read more)

Are You an Exceptional DBA?

bradmcgehee — Wed, 28 May 2008 19:44:17 GMT

Over the past 13 years that I have been a DBA, I have had the opportunity to meet hundreds of other DBAs through the website I started, at my place(s) of work, at other companies, at user group meetings, and at conferences. I have meet DBAs who are new to the career, others who are experienced SQL Server MVPs, and everything in between. One thing that really has stood out when talking with other DBAs is that there are no sources of information on how to become a DBA. In other words, there are no...(read more)

Refactoring Stored procedures on a SQL Server database?

satya — Mon, 26 May 2008 08:00:00 GMT

I would like to refer this Interesting thread that talks about advantages you can take with Profile when you have a need of refactoring the database objects between old & new versions, useful tips on the post too....(read more)

Best practices/Gotcha's when migrating 2k DB's to 2K5 box? (also 2K->2K)

satya — Sun, 25 May 2008 01:56:00 GMT

Here is another interest forum post that will talk through the best practices, gotchas and FAQ when you are performing a database upgarde from SQL Server 2000 to 2005 version. Post link ....(read more)

Orpahned users? Use this and clean it all up!

MichaelB — Fri, 23 May 2008 18:11:00 GMT

Ever restore (a) database(s) which have users that are on the server you are restoring to? When you look at the users, they dont have the database assigned to them, but they are in the security area of the database you restored. This is a issue where the userids do not synch up from one server to another. If you use my script it will go through the entire server and fix these issues. This usually happens when doing database restores from prod to dev. If there are no orphans, then the script wont return a thing!

set nocount on
go

DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')
OPEN Curse

FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + '
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse

SQL Server Video Training is Becoming All the Rage

bradmcgehee — Tue, 20 May 2008 00:43:20 GMT

Over this past year, I have noticed how popular online video training has become. Video training on virtually any topic can be found, anywhere from free, to nominal cost, and even high dollar. All you have to do is to do an Internet search for the topic of interest, and include "video training" as part of your search phrase. As a SQL Server DBA, I find myself spending a lot of time self-learning. Most of my time is spent reading books and practicing. I have tried some conventional, web...(read more)

SQL Server installation error 703 : An INternal read error occurred on media , unable to load the installation information...

satya — Mon, 19 May 2008 07:42:00 GMT

One of the forum user reported about an error they are getting when trying install SQL Server 2000. By default you need to have Administrator rights on the server where you are trying to install the SQL Server binaries, in this case the user has used the login with Domain Admin and local admin rights, still the error persists. For resolution refer to the RelevantForum post itself....(read more)

SQL Server Performance issue - high value for Full Scans/sec?

satya — Thu, 15 May 2008 08:57:00 GMT

One of the intersting post in forums and replies to them: I recently took a new position as the DBA for a web app company. Getting to understand the performance issues that are a major concern for the company I have started monitoring the servers. The DB server so far does seem to be a little bit overloaded and I am working on reducing the workload. One of the counter (Full Scan/Sec for SQL Server Access Methods) is running way above that it should be. I am recording averages of 150-200 Full Scans...(read more)

SQL Server 2008 Editions

Greg Larsen — Fri, 09 May 2008 14:16:00 GMT

Over the last few months Microsoft has been having many different launch events worldwide for their 2008 products: Visual Studio 2008, Windows 2008, and SQL Server 2008. I had the opportunity to attend one of these launch events. Being a SQL Server DBA I attended the Data track of their rollout. In this track they discuss all the new features that are being incorporated into SQL Server 2008. What this presentation missed was discussion related to the different editions available with SQL Server 2008, and which features where available by edition. To me it seems like a lot of the cool new features all seem to be implemented in the Enterprise edition. But there are still a number of exciting new features in the other editions as well. Here are some links you can use to compare the features by edition.

Compare the Enterprise and Standard Edition:
http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

Comparing Compact and Express edition:
http://download.microsoft.com/download/e/8/8/e8859616-e95d-41fe-9f81-ff88388d772b/SQLServer%202008CompareComapctExpress.pdf

SQLTeach & DevTeach Coming to Toronto in May

bradmcgehee — Sun, 27 Apr 2008 00:11:51 GMT

If you live in Canada, or nearby Toronto, consider attending SQLTeach and DevTeach this May. I will be presenting three sessions on SQL Server you might want to check out: DBA 101: Best Practices for New DBAs How to Use SQL Server Hints Introduction to the SQL Server 2008 Performance Data Collector Besides my sessions, check out the following features: DevTeach is innovating with its Silverlight 2.0 workshop in Toronto For the first time an independent conference is having a workshop on Building...(read more)

SQL Server Patch Management Methodology

Greg Larsen — Thu, 24 Apr 2008 11:51:00 GMT

As most of you probably already know Cummulative Update #7 for SQL Server 2005 was released on April 17^th. (http://support.microsoft.com/kb/949095). Also Microsoft has now said they will be providing a Service Pack 3 sometime after SQL Server 2008 has been release to manafacture (http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx). So one way or another you should consider how you plan to patch your SQL Server 2005 machines.

As I see it there are three different patch management strategies you can take. The first option is every 8 weeks when a new Cummulative Update comes out from Microsoft install it. This will keep you up to date with all the fixes close to when they come out. The second option is to only apply the specific hotfixes that you need to resolve what ever problems you encounter. And now with Server Pack 3 coming out in the future you have a third option, and that is wait until SP3 comes out and apply it.

Now in our shop we have decided to do use a combination of option 2 and 3. We have decided this because of this language in the Cummulative Updates:

A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.

We feel that if all fixes in a Cummulative Update have not be thoroughly tested then we don’t want to apply the Cummulative Update. Although if we have a problem that is fixed with a particular hotfix then we will apply that hotfix patch.

Lastly we have decided that when applying a hotfix we will setup a test environment to verify the hotfix does in fact fix a problem we have identified. If we can’t verify it fixes a problem, and/or causes additional problems it will not be rolled into our production environment. When we apply Service Packs we also apply it in a test environment first. But we also will perform some additional application testing to verify nothing breaks due to rolling out a Service Pack.

So how are you applying Cummulative Updates, and what is your Patch Management Methodology?

GIS Data Types in SQL Server 2008

Greg Larsen — Wed, 16 Apr 2008 01:19:00 GMT

SQL Server 2005 has some new GIS data types. How many of you are GIS savvy? Well I’ll by the first one to admit that I don’t work with GIS data. But I am starting to look at SQL Server 2008 and trying to encourage our GIS guy to look at the new GIS data types within SQL Server. Today the Redmond Magazine has an article where John Baleja, senior product manager at mapping software provider ESRI says “…will be easier integration with other applications, more security and possibly better performance.” Now as I already said I know very little about GIS data, but this statement by an ESRI management type would lead me to believe that have GIS data types in SQL Server 2008 might be a big deal, a good thing, and something worth looking at. When I told our GIS guy about this article, the GIS data types, and suggested he might want to look at how SQL Server 2008 might support storing his layers of GIS data he said something like “I must not have enough to do if I’m thinking how SQL Server 2008 will support GIS”. I see these new data GIS data types as an opportunity to determine if we can leverage SQL Server 2088 to better support our existing environment. I’m interested in hearing your comments about the new Spatial Data types in SQL Server 2008. Will these new data type really make your environment more security, and provide a boost in performance for your GIS data stores? For you GIS types out there do you see value in migrating your GIS data into SQL Server 2008?

Three New Visual Studio 2008 MCTS Exams Announced

Greg Larsen — Tue, 15 Apr 2008 03:25:00 GMT

Three Visual Studio 2008 MCTS came out today (see below). Passing one of these exams along with the Exam 70-536 TS: .NET Framework, Application Development Foundation are need to get ceritfied.

Exam 70-502 TS: .NET Framework 3.5, Windows Presentation Foundation Application Development http://www.microsoft.com/learning/exams/70-502.mspx

Exam 70-503 TS: .NET Framework 3.5, Windows Communication Foundation Application Development
http://www.microsoft.com/learning/exams/70-503.mspx

Exam 70-504 TS: .NET Framework 3.5, Windows Workflow Foundation Application Development
http://www.microsoft.com/learning/exams/70-504.mspx

More testing opportunities, Greg, MCITPhttp://sqlservexamples.com

SQL Server Networking

Greg Larsen — Sat, 12 Apr 2008 18:31:00 GMT

Did you think this post had something to do with the routers, switches and wires connecting SQL Server machines to the World Wide Web? If so you are wrong. This post has to do with my experience working one of the vendor booths at the “Heroes Happen Here” launch last week. For those of you that don’t know what the “Heroes Happen Here” launch is all about let me give you the scoop. These are events held all over the world , and this is where Microsoft is communicating to the IT community all the great things that about the new Visual Studio, Window Server and SQL Server 2008 versions have to offer. If you need more information about these events and want to find a launch near you check out this website: http://www.microsoft.com/heroeshappenhere/register/default.mspx

This blog entry has nothing do with the new 2008 products, but has more to do with communicating my experience working the PASS booth at the Seattle “Heroes Happen Here” launch. For those of you that don’t know about PASS, it stands for Professional Association for SQL Server. It is a worldwide community dedicated to Microsoft SQL Server professionals. PASS holds a summit in Europe and the United States once a year. Each of these summit events brings together thousands of SQL Server professionals to discuss SQL Server issues, topics, and events. These summit events are held over a number of days and provide some of the best training opportunities, from SQL Server gurus and normal SQL Server DBA/developer types like you. The next summit will be held in Seattle on November 17-22 2008. If you are interested in knowing more about PASS or signing up for the summit at a limited time reduced rate check out the PASS website here: http://sqlpass.org

Not only does PASS have two annual summits on both sides of the Atlantic Ocean but the PASS community also consists of over 80 local PASS user group chapters. These local user groups are scattered all over the globe. Each local user group is run a little different but they all have one thing in common, they hold routine local user group meetings to discuss SQL Server related topics. These meetings typical have one or two SQL Server related presentations per meeting. These local PASS chapter meetings are a great place to learn more about SQL Server and/or share your experience with other SQL Server types in your region of the world.

Ok, now back to may PASS booth experience at the Seattle launch. While at the PASS booth hundreds of people stopped by asking what PASS was all about. This was not really surprising in itself because many people that use Visual Studio and/or Windows Server don’t all have SQL Server in their environment. But what surprised me the most was the number of SQL Server individuals that never knew there was a PASS organization, much less that there were local PASS user group chapters right in their own backyard. I’m a local PASS chapter leader in Olympia, Washington so working the PASS both allowed me a great opportunity tospread the word about my PASS local chapter, the PASS organization, as well as time to talk with developers and other DBAs about their SQL Server Experience.

If you are reading this post you probably have a vested interest in SQL Server and learning more about it. Do you know the location of the closest PASS chapter user group is in your area? These local PASS chapter groups are a great opportunity to network with other SQL Server geeks, and get some great training at the same time. Did I mention that most of these user group opportunities are free. So if you don’t already know where the nearest PASS chapter is in your part of the World then you should check out the “Chapters” link on the PASS website above.

If you’d like to know more about local PASS chapters, and can’t find out everything you need to know about it on the PASS website, drop me a note at gregalarsen@sqlpass.org . I’ll do my best to answer your questions, and if I can’t answer them I’ll find someone that can.

Are you a member of PASS? Do you belong to a local PASS chapter? If not what is stopping you? Join the PASS family now and start receiving great SQL Server training and information.

Happy PASS Networking,

Greg Larsen, MCITP
http://sqlserverexamples.com