Email Article To a Friend View Printable Version

Table Column Info or Sample Rows with a Single Key Press

SQL Server 2005

Irena Kennedy has a neat trick for SQL Server Management Studio to quickly display some rows from a table, or get the metadata on columns of a table: a keyboard shortcut that is fast and works in any database.

Has anyone else created similar keyboard shortcuts like this? Leave your comment here.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz




Email Article To a Friend View Printable Version

Inside DBCC CHECKDB... How does it work?

SQL Server 2005

Paul Randal has been writing an excellent series on how DBCC CHECKDB works within SQL Server.

Explaining how SQL Server 2000 and 2005 present a consistent view of the data for checking and why that is important. Paul moves onto what CHECKDB really does and then Checking Database Consistency in Very Large Databases (VLDB).

Paul Randal worked as the Lead Developer / Manager for a team working on the SQL Server Storage Engine responsible for DBCC.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Checking Database Consistency in Very Large Databases (VLDB)

SQL Server 2005

Paul Randal who was previously the Lead Developer / Manager for a team working on the SQL Server Storage Engine, has written a short article on the challenges and some options for using DBCC CHECKDB in a VLDB.

If you are looking after databases in the hundreds of GB or more than a TB, read these tips from someone who knows the real inner workings of SQL Server.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Find a SQL Server 2005 MVP

SQL Server 2005

Need an MVP? Or just want to verify someone's status? Microsoft's MVP Awardees search can help you out.

Here's the current list of SQL Server MVPs in Australia and the worldwide list. Not all MVPs are on there, quite a few choose to hide from the limelight.

Not on there yet? (Me either). Become visible in the community. Participate in the Microsoft Technical Forumsms, the Microsoft Newsgroups, attend local SQL User Groups, Camps and get noticed.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Free SQL Scripts for Managing and Monitoring SQL Server 2005

SQL Server 2005

I came across these scripts on the Microsoft TechNet Script Centre

Microsoft SQL Server 2005



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Optimising SQL Server 2005 TempDB

SQL Server 2005

Microsoft's whitepaper on Optimising TempDB in SQL Server 2005 contains a lot of useful information for anyone trying to push the performance of SQL Server 2005. Along with the internal improvements to tempdb, there are a number of things a database administrator should do as part of any installation:

  • Increase the number of data files in TempDB to match the number of CPUs configured for the SQL Server 2005 instance. Each data file should be the same size.
  • Set the starting size of TempDB to an appropriate value. Use your existing TempDB size as a guide.
  • Enable AutoGrow for TempDB's log and data files to handle unexpected growth. Set filegrowth to an approriate increment: 10% for for a filesize of 500MB or more
  • Place TempDB on dedicated disks, separated from all other databases.
  • Monitor TempDB's space usage with the following performance counters:
    • SQL Server:Databases: Log File(s) Size(KB) (tempdb)
    • SQL Server:Databases: Log File(s) Used (KB) (tempdb)
    • SQL Server:Databases: Data File(s) Size(KB) (tempdb)
    • SQLServer:Transactions: Free Space in tempdb (KB)
    • SQLServer:Transactions: Version Store Size (KB)
    • SQLServer:Transactions: Version Generation Rate (KB/s)
    • SQLServer:Transactions: Version Cleanup Rate (KB/s)
  • Monitor TempDB's I/O performance with the following performance counters:
    • PhysicalDisk: Avg. Disk Queue Length
    • PhysicalDisk: Avg. Disk Sec/Read
    • PhysicalDisk: %Disk Time
    • PhysicalDisk: Disk Reads/Sec
    • PhysicalDisk: Disk Writes/Sec
    • SQLServer:Databases: Log Bytes Flushed/sec
    • SQLServer:Databases: Log Flush Waits/sec
  • Monitor contention with the following performance counters:
    • SQLServer:Access Methods: Worktables Created/sec
    • SQLServer:Access Methods: Workfiles Created/sec
    • SQLServer:Access Methods: Worktables From Cache Ratio
    • SQLServer:General Statistics: Temp Tables Creation Rate
    • SQLServer:General Statistics: Temp Tables For Destruction

A Microsoft Word copy of the whitepaper is available as well.

I've created a Performance Monitor html template that includes all the above performance counters. To use the template, save this page to your computer. You can import it into Performance Monitor by right clicking Counter Logs and choosing New Counter Settings From. Remember to re-add the Physical Disk counters for the drive that TempDB resides on.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Error: SQL Server Replication Requires The Actual Server Name

SQL Server 2005

If while trying to setup a new subscription for SQL Server 2005 Replication using Management Studio you receive this error:

TITLE: New Subscription Wizard
------------------------------

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink? ProdName=Microsoft+SQL+Serve &ProdVer=9.00.1399.06 &EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR &EvtID=ReplicationNotInstalled &LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'servername\instance'. (Replication.Utilities)

To fix this error you need to correct @@SERVERNAME so that it matches your actual machine\instance name.

Download the sql script to fix your @@SERVERNAME. Read more about fixing your @@SERVERNAME.

Related MSDN Article.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

How to fix @@SERVERNAME in SQL Server 2005

SQL Server 2005

It seems to happen from time to time: your network engineers decide on a new network topology or naming scheme and they want to rename one or more of your SQL Server machines or worse your desktop machine!

Renaming a SQL Server instance is not as simple as renaming the computer in My Computer Properties. After you've restarted windows, you will find that while the instance is now available on the network with the new machine name, internally @@SERVERNAME will probably still be using the old name. This will upset a number of features within SQL Server and Management Studio and some 3rd party tools.

Using the following sql, you can see if @@SERVERNAME has the incorrect value:

SELECT @@SERVERNAME As [@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

Both @@SERVERNAME and RealInstanceName should be identical. After a recent name change my results looked like this:

@@SERVERNAME RealInstanceName --------------------------------- --------------------------------- IAUTO-124F92 JKUITERS-DEV

To correct @@SERVERNAME for a default instance use the following commands:

exec sp_dropserver old_name
GO
exec sp_addserver new_name, 'local'
GO

To correct @@SERVERNAME for a named instance:

exec sp_dropserver old_nameinstancename
GO
exec sp_addserver new_nameinstancename, 'local'
GO

Download the sql script to fix your @@SERVERNAME.

Related MSDN Article.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 Bulk Load 1TB in under an hour

SQL Server 2005

Going through my reading material today, I came across this post by the Microsoft SQL Server Development Customer Advisory Team on using SQL Server 2005 Enterprise Edition to load 1TB (yes terabyte) in less than an hour. Mind you they did have an a HP Itanium2 Superdome with 64 CPUs, 256GB RAM and a SAN rated with 14GB per second throughput. Wow! Its interesting to note that the bottleneck in their processing was CPU.

 Run as many BULK INSERT processes as you have CPUs

The number and size of files they processed are very different to what I process, but I agree with the best practices that Kevin suggests: run as many BULK INSERT processes as you have CPUs. SQL Server will attempt to distribute the load across your available CPUs as it will consume one CPU per BULK INSERT thread. If you are bulk loading thousands of smaller files, I've found the same is still true. Running more BULK INSERTs than CPUs does not appear to increase performance.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Google searches for SQL Server 2005

SQL Server 2005

While tracing through my search history today, I came across the Google Trends tool. Its interesting to see the comparison of searches for SQL Server 2000 verses SQL Server 2005. It will be interesting to see if in the coming months just how many more searches will be for 2005 as there are more installations and developers undergoing the learning curve.

Most developers I know are already working in mixed environments, and as the chart might indicate, there's hasn't been a wholesale conversion to SQL Server 2005 just yet.

SQL Server 7 on the other hand is definitely fading.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz







Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.