SQL Server - General
SQL Saturday is coming to Phoenix, Arizona on February 19th, 2011. I have submitted my session for this event. If my session gets approved, it’ll be the same presentation that I gave recently at SQL Saturday #55. Although my recent presentation was well received, I will be reworking it to make it even better.
Hope to see you there!
I started my IT career as a student worker in the database team at the County of San Diego. Although I worked on many different things in that group, it launched my career as a Database Administrator. You can get an overview of my career here. It seems I picked the right career for a good job in America. According to CNNMoney and PayScale, a database administrator job is ranked number 7 for the best job in America in 2010. Check it out here. There are quite a few IT jobs on the list. You can check out...
On Saturday (9/18), I presented "Performance Tuning with Traces" at SQL Saturday #55. There were about 65 people in attendance, and it was standing room only. As promised, you can download my presentation materials here. I received such good feedback after my presentation that I will likely present again in the future if given the opportunity.
If you are interested in finding out the largest SQL projects in the world, you should check out this PowerPoint presentation. It’s from Kevin Cox of SQL CAT at Microsoft.
SQL Saturday is coming to my hometown, San Diego, on September 18th, 2010. I have submitted my session and hope that it gets approved. Let me know if anyone is attending the event in San Diego. If you aren’t in Southern California, check their site for the location nearest you.
Paul Randal recently blogged about three life-changing events that brought him to where he is today. His blog is a very interesting read and idea, and so I bring to you my three events that brought me to where I am today in my technical career as a SQL Server DBA.
Right out of high school and during the first two years of college, I worked as a student worker for the District Attorney's Office at the County of San Diego performing data entry and other forgettable tasks. During this job, I worked with a previous classmate whose mom worked in another...
My Database Growth Tracker tool is featured in a SQL Server Magazine web article that Kevin Kline wrote.
Check out the web article here!
If you've ever had to copy large files on a Windows platform using the Windows copy method (copy/paste in Windows Explorer or copy/xcopy commands), then you know how slow it is. Recently I had to setup database mirroring for a largish database, so I needed to copy the full backup to the mirror server. The database is about 110 GB in size, but since we use Quest's LiteSpeed product, the full backup is just 35.8 GB in size. I had heard about FastCopy, which claims to be the fastest Windows copy product, so I decided to do comparison tests. ...
Microsoft published the ERD for the system views about a month ago, but I'm just now seeing it. It is available in PDF or XPS format.
You can download them here.
Microsoft has released SQL Server 2005 service pack 3 and cumulative update package 1 for service pack 3. For more information about SP3, check this out. For more information about SP3 CU1, check this out. If you upgrade to just SP3, your SQL Server version will be 9.00.4035. If you upgrade to SP3 CU1, your version will be 9.00.4205.
Make sure you also update Books Online on your client machine. You can get the newest version of BOL here.
Has anyone used SQL Server FineBuild before? According to the web site, it "provides 1-click install and best-practice configuration of SQL Server 2005."
It doesn't mention anything about clusters though, which is what I'd be interested in as installing SQL Server on a cluster is a bit of a pain.
Do you know of other tools that simplify the installation of SQL Server? Do they work on clusters?
There's a lot of information out there on how to avoid SQL injection attacks, but I wanted to point you to this blog due to the recent increase in such attacks.
Thank you, Buck Woody, for bringing this to our (SQL Server MVPs) attention.
In a previous blog, I mentioned a performance problem that I've been working on for a few weeks. I can finally say that this issue has been resolved. The problem was that the database server (SQL Server 2005) was running at 95%-100% CPU utilization at all times.
Here is the query that was causing our problem (object names have been changed):
SELECT Table1Id
FROM Table1
WHERE Unit = ?
We were receiving a bad execution plan for this query due to nvarchar being used for the Unit parameter. The Unit column is defined as varchar(50) in the table. See the below demonstration for the...
I'm proud to announce that I have received the Microsoft MVP Award for my contributions to the SQL Server community.
I'd like to thank Ben Miller from Microsoft for the nomination. Thanks Ben!
Microsoft announced yesterday that the new version of SQL Server, code-named "Katmai", will be delivered in 2008.
Check this out for information on "Katmai".
We don't use the default port for SQL Server for security reasons, so we usually have to explicitly tell the client how to connect to SQL Server by creating an alias on the client machine.
There are many ways to create an alias on a client machine. My favorite is via the registry as it allows me to save the values to a reg file so that we can quickly deploy them to other client machines.
Here is the location of these aliases:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
Yesterday I was configuring transactional replication between two databases on different servers. The servers are on different...
Check out this article for white papers, scripts, and tools on SQL Server 2005 best practices.
We've been having performance problems on a new 64-bit SQL Server 2005 cluster in our test environment. We opened a ticket with Microsoft to assist us with this.
After modifying several things, the only thing that was slow was Management Studio. On both nodes of this cluster, it would take about 3-5 minutes to load Management Studio. The first MS engineer suggested that we add the nosplash switch to the Management Studio shortcut, like this:
This did not increase performance at all on either node. The second MS engineer suggested that we uncheck two options in Internet Explorer's Advanced Security settings. ...
In SQL Server 2000, you could generate a SQL script easily using the Generate SQL Script wizard. You could get to it from pretty much anywhere from within Enterprise Manager. For instance, you can get to it by right clicking on any object in the database, then selecting All Tasks, and then selecting Generate SQL Script.
In SQL Server 2005, it is now called Generate Scripts. The only place that I can find where you can get to it is if you right click on the database, then selecting Tasks, and then selecting Generate Scripts. The 2005 wizard does not...
In Query Analyzer, we could easily comment blocks of code using Ctrl+Shift+C and uncomment blocks of code using Ctrl+Shift+R.
These keyboard shortcuts do not work in Management Studio unless you switch the keyboard scheme to SQL Server 2000.
In Management Studio, you can comment blocks of code using Ctrl+K, Ctrl+C and uncomment blocks of code using Ctrl+K, Ctrl+U. These are the same keyboard shortcuts that you would use to comment blocks of code in Visual Studio .NET, so I'm sure that's why Microsoft decided to switch the shortcuts.
To see other keyboard shortcuts for T-SQL code in Management Studio, open a query window...
Last week, I received a new laptop. For the past few days, I've been installing all of the applications that I need. Today, I decided to tackle Red Gate's SQL Compare. On my old laptop, I was running version 3. I hadn't kept a copy of the installation, so I went to their site to see if I could download it. I couldn't find version 3, so I decided to download the new version to see if my serial number would work as I wasn't sure if we had renewed our support contract. My serial number didn't work in version...
Some of our developers regularly debug stored procedures in SQL Server 2000. In fact, they can't live without it. The next version of their application will use SQL Server 2005.
Today, one of the developers asked me how to use SQL Debugging in SQL Server 2005. I barely even know how to use it SQL Server 2000, but I wanted to help him so I started reading about it on MSDN.
In SQL Server 2000, I had to grant execute permission on master.dbo.sp_sdidebug extended stored procedure in order for them to debug T-SQL code. According to this article, the connection account must...
Which SQL Sever 3rd Party tools do you use?
We've been using Red Gate's SQL Compare and SQL Data Compare for a few years now. I rarely use the data compare tool though, but not because it's not a great tool. We also use Quest's SQL LiteSpeed product. It reduced our backup files and backup times by 75%! The disk savings on our SAN was huge.
We are considering purchasing a few Idera products. I've recommended their SQLschedule product to a group here that manages over 250 jobs across 2 database servers.
If you aren't using any 3rd Party tools, is...
Paul Flessner, Senior Vice President at Microsoft, announced this morning in his Tech.Ed keynote that SQL Server 2005, Visual Studio 2005 and BizTalk Server 2006 will launch the week of November 7.
Service pack 4 for SQL Server 2000 was released this morning. Check this out for details.
Get Ready for SQL Server 2005
“Get the facts about migrating to SQL Server™ 2005 in one information-packed day in a city near you! SQL Server experts from Scalability Experts, DevelopMentor, and Hitachi Consulting will present practical, real-world information in three tracks—administration, development, and business intelligence. You can find answers to specific questions in the Ask the Experts session. You'll come away from the Get Ready for SQL Server 2005 Roadshow with a clear understanding of how to implement a best-practices migration to SQL Server 2005 and how to use SQL Server 2005's new capabilities to improve your database computing environment.“
I'll...
Over in the SQLTeam forums , we had a long discussion/debate about paging in SQL Server 2005 . Adam Machanic shows us his solution to this problem.
Benjamin Jones shows us an example of using extended triggers to capture DDL statements. Very cool!
For those of you wondering...DDL - data definition language - examples: CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX
A common mistake when first creating a trigger is thinking that the inserted or deleted tables will contain only one row. This is not true. However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table. So you must code the trigger to handle multiple rows. In the forums of SQLTeam.com, we often see trigger code like this:
CREATE TRIGGER trg_Table1 ON Table1 For UPDATEAS
DECLARE @var1 int, @var2 varchar(50)
SELECT @var1 = Table1_ID, @var2 = Column2FROM inserted
UPDATE Table2SET SomeColumn = @var2WHERE Table1_ID = @var1
The above trigger will only work for the last row in...
If you've got an MSDN subscription, head on over to MSDN to download SQL Server 2005 Beta 2.
The iso image file is 595.5 MB. I downloaded it this morning in under 30 minutes. Now I've just got to install it.
You've probably already heard by now about SQL Server 2005 Express Edition. It's the next evolution of MSDE 2000.
Here's the Top 10 Cool Things about SQL Server 2005 Express Edition:
Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.
Native XML support.
XCopy support for moving databases from one location to another.
Robust and reliable installation using either graphical user interface (GUI) or silent modes.
Automated servicing and patching.
Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.
Support for databases up...
Why is it that you can pass a built string to xp_cmdshell but you can't build your string at the same time. What I mean is this:
This is legal:
DECLARE @cmd VARCHAR(255)DECLARE @ServerName SYSNAME
SET @ServerName = 'Server1'SET @cmd = 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\'
EXEC master.dbo.xp_cmdshell @cmd
This is not legal:
DECLARE @ServerName SYSNAME
SET @ServerName = 'Server1'
EXEC master.dbo.xp_cmdshell 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\'
Output:
Server: Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near '+'.Any comments?
Duane Dicks has a script that can display an analog clock in Query Analyzer.
Make sure your results are in text and your font is Courier.
MS updated SQL Server 2000 Books Online again:
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
I recently had to create a UDF that would convert a Java time value to a datetime value. We have an application that stores dates and times as the number of milliseconds since Jan. 1, 1970. Well that isn't very pleasant to the human eye when trying to debug things. So, here is a function to convert it to datetime:
CREATE FUNCTION udf_ConvertJavaTimeToDateTime(@milliseconds BIGINT)RETURNS DATETIMEAS
BEGIN
DECLARE @DateTime DATETIME
SELECT @DateTime = DATEADD(s, @milliseconds/1000, '1970-01-01 00:00:00.000' )
RETURN @DateTime
END
And here is a function to convert a datetime value to Java time:
CREATE FUNCTION udf_ConvertDateTimeToMSSince1970(@DateTime DATETIME)RETURNS BIGINTAS
BEGIN
DECLARE @seconds BIGINT
SELECT @seconds = DATEDIFF(s, '1970-01-01 00:00:00.000', @DateTime)
RETURN...
So how do you DELETE thousands/millions of rows from a table without blocking anyone for long periods of time?
Well, here is an example:
DECLARE @Count INT DECLARE @Error INT
SELECT @Count = COUNT(*)FROM SomeTable MLEWHERE SomeDateColumn < (GETDATE() - 182) -- anything older than 6 months
SET ROWCOUNT 10000
WHILE @Count > 0BEGIN
BEGIN TRAN
DELETE FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182)
SELECT @Error = @@ERROR
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
CHECKPOINT
SELECT @Count = COUNT(*)FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182)
END
SET ROWCOUNT 0SET NOCOUNT OFF