posts - 220, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

SQL Server Stuff

Error: 18456, Severity: 14, State: 11

I’ve seen this error off and on in our environment for a while.  The full error looks like this: Login failed for user DOMAIN\login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: X.X.X.X] Error: 18456, Severity: 14, State: 11. Aaron Bertrand wrote the post I always start with on troubleshooting 18456 errors.  I’ve always struggled tracking down the root cause of state 11 though.  I’ve seen it intermittently occur and then clean itself up.  One of the reported causes is difficultly connecting to a domain controller so that kind...

posted @ Wednesday, October 08, 2014 2:20 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

How to Name Linked Servers

I did another SQL Server migration over the weekend that dealt with linked servers.  I’ve seen all kinds of odd naming schemes and there are a few I like and a few I suggest you avoid. Don’t name your linked server for its IP address.  At some point whatever is on the other end of that IP address will move.  You’ll probably need to point your linked server to a new IP address but not change the name of the linked server.  And then you’ve completely lost any context around this.  Bonus points if a new SQL Server eventually...

posted @ Monday, August 15, 2011 4:03 PM | Feedback (6) | Filed Under [ SQL Server Stuff ]

How are Reads Distributed in a Workload

People have uploaded nearly one millions rows of trace data to TraceTune.  That’s enough data to start to look at the results in aggregate.  The first thing I want to look at is logical reads.  This is the easiest metric to identify and fix. When you upload a trace, I rank each statement based on the total number of logical reads.  I also calculate each statement’s percentage of the total logical reads.  I do the same thing for CPU, duration and logical writes.  When you view a statement you can see all the details like this: ...

posted @ Monday, February 21, 2011 9:00 AM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

Generate MERGE statements from a table

We have a requirement to build a test environment where certain tables get reset from production every night.  These are mainly lookup tables.  I played around with all kinds of fancy solutions and finally settled on a series of MERGE statements.  And being lazy I didn’t want to write them myself.  The stored procedure below will generate a MERGE statement for the table you pass it.  If you have identity values it populates those properly.  You need to have primary keys on the table for the joins to be generated properly.  The only thing hard coded is the...

posted @ Tuesday, February 15, 2011 7:47 AM | Feedback (1) | Filed Under [ SQL Server Stuff Utilities ]

Enter comments on queries in TraceTune

I’m trying to make TraceTune (and eventually ClearTrace) work the way I do.  My typical query tuning session goes like this: Run a trace and upload to TraceTune/ClearTrace Tune the slowest queries Goto 1 I might do this two or three times in one day and then not come back to it again for weeks or even months.  This is especially true for those clients that I only visit a few times per month.  In many cases I’ll look at a query, decide I can’t...

posted @ Monday, February 07, 2011 7:48 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Script to UPDATE STATISTICS with time window

I recently spent some time troubleshooting odd query plans and came to the conclusion that we needed better statistics.  We’ve been running sp_updatestats but apparently it wasn’t sampling enough of the table to get us what we needed.  I have a pretty limited window at night where I can hammer the disks while this runs.  The script below just calls UPDATE STATITICS on all tables that “need” updating.  It defines need as any table whose statistics are older than the number of days you specify (30 by default).  It also has a throttle so it breaks out of...

posted @ Thursday, December 02, 2010 5:21 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Will you share your SQL Server configuration?

I regularly visit client sites and review their SQL Server configurations.  I come across all kinds of strange settings.  I’ve been thinking about a way to aggregate people’s configurations and see what’s common and what’s unique.  I used to do that with polls on SQLTeam.com.  I think we can find out more interesting things if we look at combinations of settings in relation to size and volume. I’ve been working on an application for another project that is similar.  It will be fairly easy to use that code for this.  I can have something up and running in a...

posted @ Monday, October 25, 2010 8:06 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Scripting out SQL Server Logins

I regularly move logins between servers.  Mostly this is between production and our DR site.  I’ve used the code in KB246133 many, many times but it’s pretty limited.  I started with that and wrote the script below. There is a user-defined function that’s created in master.  You can create it in any database you want but you’ll need to update the script.  The function converts varbinary hashed passwords to a string representation. It keeps the password intact for SQL Server logins. It scripts both Windows logins and SQL Server logins. ...

posted @ Thursday, July 08, 2010 11:09 AM | Feedback (2) | Filed Under [ SQL Server Stuff Utilities KCTechBlog Syndication ]

Lessons from a SAN Failure

At 1:10AM Sunday morning the main SAN at one of my clients suffered a “partial” failure.  Partial means that the SAN was still online and functioning but the LUNs attached to our two main SQL Servers “failed”.  Failed means that SQL Server wouldn’t start and the MDF and LDF files mostly showed a zero file size.  But they were online and responding and most other LUNs were available.  I’m not sure how SANs know to fail at 1AM on a Saturday night but they seem to.  From a personal standpoint this worked out poorly: I was out with...

posted @ Friday, February 26, 2010 8:29 AM | Feedback (2) | Filed Under [ SQL Server Stuff KCTechBlog Syndication ]

SQL Server Blogs I Read

Peter Larsson recently scrubbed his laptop but didn’t bring over his favorites and made a plea for interesting SQL Server related blogs.  My reading list of SQL Server blogs and sites is up to 83 sites in Google Reader and I thought I’d share my list in hopes of finding more.  Here’s the link to the OMPL file that you can import into a feed reader: SQL Server Blogs OPML file. Below is the complete list of SQL Server blogs I read regularly.  If you’re writing about SQL Server and not on that list please use the contact me...

posted @ Friday, February 05, 2010 7:16 AM | Feedback (2) | Filed Under [ SQL Server Stuff KCTechBlog Syndication ]

SQL Server Links - 19 January 2010

The Bit Bucket (Greg Low): IDisposable : Stored Procedures - Time for a real contract? [sqlblog.com/blogs/greg_low/] Interesting case of watching log file growth during a perf test [www.sqlskills.com/BLOGS/PAUL/] Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency [blogs.msdn.com/sqlserverstorageengine/] Microsoft and Intel Push One Million iSCSI IOPS [blog.fosketts.net] The Truth About Hyper-V Memory Overcommit [www.vcritical.com] A Few Useful Queries for SQL Server 2008 Integrated Full Text Search (iFTS) [glennberrysqlperformance.spaces.live.com] SYS2 DMVs on CodePlex [sqlblog.com/blogs/davide_mauri/] Intel Nehalem-EX and Database Performance [glennberrysqlperformance.spaces.live.com] ...

posted @ Tuesday, January 19, 2010 9:02 AM | Feedback (0) | Filed Under [ SQL Server Stuff KCTechBlog Syndication ]

SQL Server Links – 8 Dec 2009

I’ve been posting links to interesting blog posts in the SQLTeam.com Newsletter for over a year now.  I going to try posting them here also. Oh what job was it? A procedure to search your jobs for a particular string [www.sqlservercentral.com/blogs/james_howards_sql_blog/] One Man's Trash... [www.straightpathsql.com] - I really liked this post. A great discussion about the importance of attitude at work. Bruno Terkaly - Developer Evangelist - bterkaly@microsoft.com : Deploying Azure apps in 1000 words or less [blogs.msdn.com/brunoterkaly/] Fundamentals of Storage Systems – RAID, An Introduction [www.sqlservercentral.com/blogs/sqlmanofmystery/] Fundamentals of Storage Systems –...

posted @ Tuesday, December 08, 2009 8:50 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

SQL Server 2008 SP1 CU3 and SQL Server 2008 RTM CU6 Released

Microsoft continues to release cumulative updates roughly every two months.  These two came right on schedule.  As always I’ve updated my build list in the SQL Server Version article on SQLTeam.com.

posted @ Tuesday, July 21, 2009 10:45 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Cost to Compile a Query

It’s pretty easy to determine the CPU and disk resources that a given query or stored procedure will use.  It’s more difficult to determine the resources that were used to compile that query plan.  You can start by looking at sys.dm_exec_cached_plans.  It has a column called “size_in_bytes” that will tell you how much memory the query plan is using. If you generate an XML query plan through SSMS or Profiler you can get some additional information.  The XML plan includes this snippet: <QueryPlan CachedPlanSize="196" CompileTime="53" CompileCPU="53" CompileMemory="1896"> If you review the schema for the XML query plan...

posted @ Wednesday, June 24, 2009 6:49 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

SQL Server sites I visit

Google Reader just introduced functionality to create “bundles” of feeds.  I subscribe to roughly 60 SQL Server blogs that I read on a regular basis.  You can download an OPML file of these.  More interestingly you can just subscribe to the individual RSS feeds of these sites.  If you subscribe to it in Google Reader it will create create a new tag labeled “SQL Server feeds” which is what I named my bundle.  You will be subscribed to each of the individual feeds in my bundle.  If you are already subscribed to one of the feeds it will...

posted @ Friday, May 22, 2009 7:16 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Why am I being forced to learn PowerShell?

I spent an annoying afternoon with SQL Server 2008 today.  When we installed it on our cluster it created a job called “syspolicy_purge_history”.  I assume it does this in most installations but I haven’t checked.  The job was generating an error every time it ran.  A quick Google search turned up an article on the problems with syspolicy_purge_history and clusters.  Now all I needed was to figure out the PowerShell syntax to correctly refer to this instance.  PowerShell?  Oh yes.  They couldn’t just call a stored procedure.  Oh no.  Rather than giving me the choice to...

posted @ Tuesday, May 12, 2009 7:41 PM | Feedback (4) | Filed Under [ SQL Server Stuff ]

Interviewed on RunAs Radio

I was recently interviewed on RunAs Radio.  We spent quite a bit of time talking about performance tuning.  I’m spending quite a bit of time lately looking at how things get into and out of the query cache and we touched on that a bit.  We also spent some time talking about ClearTrace and what it can do for you.  Take a listen and try not to laugh at a “voice made for print”.  I was also interviewed on Greg Low’s SQL Down Under a few years back.  That show is in the SQL Down Under archive (show #13).

posted @ Monday, April 13, 2009 7:55 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

ClearTrace updated to support SQL Server 2008 trace files

It was a long time coming but I finally updated ClearTrace to read SQL Server 2008 trace files.  This requires SQL Server 2008 to be installed on the computer where ClearTrace is running.  ClearTrace will process traces created in SQL Server 2000, SQL Server 2005 and SQL Server 2008.  ClearTrace is the tool I wrote to make performance tuning easier.  Many of my client engagements are to make SQL Server faster and ClearTrace is the tool I use to do that.  ClearTrace will read a series of trace files and aggregate the results so you can tune the...

posted @ Sunday, March 29, 2009 12:07 PM | Feedback (5) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Test your backups!

The purpose of making a backup is to be able to restore it at some point.  Many of the companies I visit do regular backups but haven't ever restored one of them.  If you really want to be safe, test the process to pull a SQL Server backup from off-site storage and restore it.  I'm watching a company do this right now and they are struggling.  We have a trouble ticket open with the backup software vendor to understand why we can't access our backups. I suggest you test your backups quarterly or twice a year.  Want a reminder? ...

posted @ Friday, January 09, 2009 8:32 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Capturing the text of a prepared SQL statement run with sp_execute

Many times when running a trace against SQL Server you'll see RPC:Completed events that look like this: exec sp_execute 66,4,4 Unfortunately this statement was the one causing me a performance problem and I had no idea what it was doing.  The 66 is the number of the statement to execute and the two 4's are the parameters passed to the statement.  The 66 is specific to a connection -- meaning the SQL is prepared for only that session.  I talked about the sequence of statements that are part of preparing and un-preparing SQL in Examining SQL Server Trace...

posted @ Friday, October 10, 2008 8:41 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Scriptio available on CodePlex

Scriptio is a little utility I wrote when I was unhappy with the scripting features in SQL Server 2005.  The biggest problem I faced was that I couldn't script a DROP and CREATE in the same file.  I'm pretty sure that's finally fixed.  The utility gives you all kind of scripting options now. The source code was always available on the site but wasn't always easy to find.  And there wasn't a good way for people to submit improvements.  I finally migrated the code base over to CodePlex.  You can download the source code or just the compiled EXE. ...

posted @ Thursday, June 05, 2008 9:12 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Are you attending the SQL Server 2008 Launch in LA?

I'll be in Los Angeles for the launch this week.  I'll be the PASS representative at the Heroes Community Booth in the Microsoft Pavilion.  When you enter the Microsoft Pavilion we'll be all the way back in the left corner.  Come by and say Hi if you get a chance.  I arrive Tuesday night and leave Thursday morning.  Is anyone else reading going to be there?

posted @ Monday, February 25, 2008 7:27 AM | Feedback (1) | Filed Under [ Events SQL Server Stuff PASS ]

SQL Server 2008 to RTM in Q3 of 2008

Francois Ajenstat posted today that Microsoft expects to release SQL Server 2008 to manufacturing in the third-quarter of 2008. Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3. Our goal is to deliver the highest quality product possible and we simply want to use the time to meet the high bar that you, our customers, expect. Their previous plan was to RTM in Q2...

posted @ Friday, January 25, 2008 3:58 PM | Feedback (1) | Filed Under [ SQL Server Stuff ]

SQL Server 2005 Best Practices Analyzer Updated (Jan 2008)

Microsoft just updated the SQL Server 2005 Best Practices Analyzer.  There are over 60 new rules included in this release.  You can find more information on the SQLCAT blog.

posted @ Saturday, January 19, 2008 9:40 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

ReadTrace for SQL Server 2005

Microsoft just released an updated Read80Trace that works on SQL Server 2005.  The name is simplified to just ReadTrace.  ReadTrace is a command-line utility that analyzes a SQL Server trace file and summarizes performance.  The new version has a more graphical interface.  The original was one of the most helpful performance tuning utilities I've ever used.  I expect this one will be just as useful. I typically use the tool to summarize performance by SQL statement.  ReadTrace will "normalize" the SQL statements by replacing constants with placeholders.  This lets you identify which SQL statements are using...

posted @ Wednesday, December 19, 2007 7:59 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

SQL Server 2008 CTP5 on Friday

It looks like SQL Server 2008 CTP5 should be available for download on Friday.  It's already in Connect for certain testing groups.

posted @ Thursday, November 15, 2007 2:41 PM | Feedback (1) | Filed Under [ SQL Server Stuff ]

Login Events include Pooled Connections

Every now and then I stumble across something and say "I know I knew this before!"  In Profiler, the Audit Login event includes regular logins and connection pooling reuse.  If you run a trace and see a large number of logins you'll need to include the EventSubClass column to determine if they are pooled connections. Even more misleading is PerfMon.  If you capture the SQLServer:General Statistics - Logins/sec counter you'll see regular logins and connection pooling reuse.  I haven't found a way to have PerfMon just show me "real" logins and not connection pooling events.  I've started watching...

posted @ Wednesday, October 31, 2007 8:52 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

Read90Trace coming soon!

Bob Ward announced in his PASS pre-conference session that Microsoft will be releasing a SQL Server 2005 version of Read80Trace in roughly 30 days.  It's currently going through a security review.  They're planning to call it ReadTrace instead of Read90Trace though.  I don't know what this will do to my plans for more work on ClearTrace.  I'll have to wait and see exactly what they release.

posted @ Sunday, September 23, 2007 10:20 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Viewing Database Updates through ODBC using Profiler

I just finished writing a small data update utility to copy a single row of data from a custom written application into a piece of packaged software.  Usually when I do this I turn on Profiler, watch the packaged software insert a row and then copy what they did.  This helps me find all the odd little status fields and related tables that aren't obvious from looking at the table.  And yes, if the developer provided documentation it would help.  They didn't. A simple login, adding one row and logging out generated over 10,000 SQL:BatchCompleted...

posted @ Friday, August 24, 2007 10:35 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Upgrading to SAS drives

We were having some disk drive performance issues at a client recently.  They have a 6 drive array running RAID 10.  We spent some time running SQLIO against the array.  Our standard test was an 8KB random read with a queue depth of eight.  The array was giving us 370 I/O operations per second with a latency of 42 ms.  We cracked open the case and discovered it was using 7200 RPM SATA drives.  We replaced the controller with the same model except that it was designed for SAS (serially attached SCSI) and installed six 15,000 RPM SAS drives.  Running the...

posted @ Friday, April 27, 2007 6:45 AM | Feedback (3) | Filed Under [ SQL Server Stuff ]

SQL Server 2005 SP2 Build 3054

The joy that is SP2 just keeps on giving.  Microsoft released a hotfix (KB934458) to bring it up to 3054: FIX: The Check Database Integrity task and the Execute T-SQL Statement task in a maintenance plan may lose database context in certain circumstances in SQL Server 2005 builds 3042 through 3053 This came down through Windows automatic updates.  It was an 18MB package and took roughly 11 minutes on my laptop.  There is also a fix (KB934459) in case you're running a higher build of SQL Server 2005 (3150 - 3158).  I'm not sure exactly how these apply or...

posted @ Thursday, April 05, 2007 8:02 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

SQL Server 2005 SP2 Available

And it is available today.  Microsoft released SQL Server 2005 SP2.

posted @ Monday, February 19, 2007 11:22 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

ClearTrace Video

I created a small video to demo ClearTrace. It's only about two minutes long. This is also the technology we're planning to use at the PASS Conference to capture presentations.

posted @ Friday, July 14, 2006 1:03 PM | Feedback (1) | Filed Under [ SQL Server Stuff PASS ClearTrace ]

Yet Another ClearTrace Update

These titles seem to be getting repetitive. I updated ClearTrace again. I'm also back to using a ClickOnce installer. Unfortunately if you switch from an unsigned to a signed ClickOnce installation the existing people can't upgrade. How annoying! That was half the reason I switched to an MSI installer. Creating the package just took too much time when I wanted to release an update. It is just so easy to click the Publish Now button and be done with it. I recently added a feature to capture some anonymous performance data. I'm trying...

posted @ Monday, July 10, 2006 8:47 AM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

ClearTrace Updated

Since I last posted here I've updated ClearTrace.  You can download it here.  There's also a ClickOnce installation if you just want to use the GUI version.  ClearTrace now supports moving the trace files to an archive directory after they're processed.  It also stores a summarized version of the trace files so it can query it faster.  The latest release also includes numerous bug fixes.

posted @ Friday, April 07, 2006 3:21 PM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

ClearTrace 2.0.9

I released ClearTrace 2.0.9.  You can find the information here.  I moved the download to my corporate site so I can keep a static page up for information.  I'm pretty happy with this version.  It seems to be very useable.  It also reports errors back to me so give it a whirl :)  There are no huge new features added but everything works a little better.  Let me know what you think.

posted @ Thursday, March 30, 2006 4:39 PM | Feedback (4) | Filed Under [ SQL Server Stuff ClearTrace ]

SQL Server 2005 Query Plan Webcast (UPDATED)

You can download the PowerPoint file and demo script from my webcast here.  I'll post a link to the recorded presentation when they post it. You can view the recorded webcast by registering here.  The layout of their site is a little interesting.  Follow any one of the links for Java, ColdFunsion or PHP and you'll end up at the list of sessions.  On the second page, choose the link for Data Access and I'm listed as “SQL Server 2005 Developement Tips and Tricks”.

posted @ Wednesday, March 22, 2006 12:25 PM | Feedback (5) | Filed Under [ Events SQL Server Stuff ]

ClearTrace 2.0.7

I've made some pretty significant changes to this tool. It now writes to multiple tables instead of a single table. This greatly reduces the space required and the import time. It creates the objects it needs in the database specified. All the objects have a CT prefix in the name. There is a view included to query them. There is GUI available now. Both the console version and the GUI version are included in the ZIP file below. They have the same code behind them. There are a few options on the...

posted @ Tuesday, March 21, 2006 8:20 AM | Feedback (2) | Filed Under [ SQL Server Stuff ClearTrace ]

Great Sample ADO.NET 2.0 Code

Pablo Castro, the Program Manager of the ADO.NET team, posted some great code samples from his PDC presentation. There are a variety of demos available. The one I spent the most time playing with was the one that used BCP on the client to bulk insert back to the server. If you need to move large amounts of data from the client to the server it's definitely worth a look.

posted @ Tuesday, January 24, 2006 6:43 PM | Feedback (5) | Filed Under [ SQL Server Stuff ]

SQL Server 2005 object scripting tool source code

I posted the source code for my SQL Server 2005 object scripting tool. The code reads like something I threw together to see if I could really do this. Because that's what it is. You can read a prior post that describes a little of what it does. The ClickOnce deployment is available here: http://www.sqlteam.com/publish/scriptio/ It's at version 0.5.1 as I type this. New features include: Connect to SQL Server using SQL Server accounts Option to include collation in the script Can optionally qualify creates, drops foreign keys with the schema Update: Most recent install and source code:  http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

posted @ Saturday, December 24, 2005 12:05 PM | Feedback (10) | Filed Under [ SQL Server Stuff Software Development Utilities ]

Scripting Objects in SQL Server 2005

After working with SQL Server 2005 I've discovered that I can't script out one object per file and include both the DROP and CREATE in the same file.  Which is driving me absolutely crazy!  So I wrote a little utility and thought I'd share it.  If you'd like to see this functionality included in a future service pack you can vote for this feature through MSDN Feedback Center. This utility requires .NET 2.0 and the SQL Server 2005 client tools installed on the machine.  The application is deployed as a ClickOnce application.  It can be installed at: http://www.sqlteam.com/publish/scriptio/ (Location Updated) I haven't released the...

posted @ Tuesday, November 22, 2005 2:58 PM | Feedback (64) | Filed Under [ SQL Server Stuff Software Development Utilities ]

SQL Server 2005 RTM Code Available

The downside of having a blog and SQLTeam.com is that I feel like I have to post some things in both places. And this is one of those things. The Developer Edition of SQL Server 2005 is "available" on MSDN. MSDN is swamped so good luck getting the download started. There are also a few editions of Visual Studio 2005 available for download.

posted @ Thursday, October 27, 2005 2:45 PM | Feedback (2) | Filed Under [ SQL Server Stuff ]

PASS Session: Future Directions in Data and Language Integration

Of all the presentations that MS submitted for the PASS Conference, this was the one that intrigued me the most. It's on Wednesday at 1:30PM and is being delivered by Alan Griver. The original abstract was fairly vague. Lots of references to data manipulation on the client side but no real hint as to what it would actually cover. After the PDC it became clear that this was a presentation on LINQ. Once the information was public Alan updated his abstract and the whole thing makes more sense. I'm really looking forward to this...

posted @ Tuesday, September 27, 2005 10:44 PM | Feedback (1) | Filed Under [ Events SQL Server Stuff PASS ]

Integrating .NET and SQL (LINQ)

From the Visual Studio Data Team blog ... At the PDC Microsoft announced LINQ (Language Independant Query) Framework.  This gives you the ability to use a SQL-like language on your collections (anything IEnumerable).  The example from the blog is: Dim SmallCountries = Select Country _From Country In Countries _Where Country.Population < 1000000 This is certainly an interesting option for collections.  This will make it much easier to cache data on the client side and use it in a flexible manner.  I've been hearing for years that we're just about to see the end of relational databases and OO databases are...

posted @ Tuesday, September 13, 2005 3:03 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

More SQL Server NAnt Tasks

I've updated my NAnt tasks to 0.94 (source and binary).  I've added three new tasks. The first is a task to load an Excel spreadsheet into a SQL Server table.  We've found this to be a fair way to manage code tables.  They're easy to edit and print out for review.  They don't handle the foreign key checking until we load them in but that's been a fair trade-off for their ease of use. The second task imports an image file into SQL Server.  It's basically a mirror image of TEXTCOPY.EXE except that it's a NAnt task and the trust connection works. The...

posted @ Sunday, May 08, 2005 2:26 PM | Feedback (4) | Filed Under [ SQL Server Stuff Software Development Database Builds ]

Making Sense of MSDN

Last year Joel (of JoelOnSoftware.com) wrote an article that had a little blurb on the “MSDN Way” of doing things.  That basically amounted to always using the newest, fanciest tools to solve a problem.  Why use VB6 and SQL Server when you can use C#, SharePoint, BizTalk, XML, Web Services and SQL Server to accomplish the exact same thing.  I chuckled at that but basically agreed with his characterization of MSDN.  I'm still a big fan of the magazine though.  Tomorrow's C#, SharePoint, etc. is today's VB6 and SQL Server.  It's a great place to get a handle on what's...

posted @ Thursday, May 05, 2005 12:21 AM | Feedback (5) | Filed Under [ SQL Server Stuff Site Related ]

Making Enterprise Manager faster with lots of databases

I wish I remember where I saw this posted.  I owe mad props to someone but I can't remember who.  If your server has lots of databases that you don't have access to and you connect using Enterprise Manager you may have noticed that it takes a loooong time to populate the Databases tab.  This especially applies in the hosting world when you're dealing with a shared SQL Server. MS released a fix that dramatically improves performance in this situation.  I finally got to try it out when ORCSweb installed it.  Wow!  It's quick!  And of course, this gives me another chance to...

posted @ Thursday, April 21, 2005 4:22 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

NAnt Task to Restore a SQL Server Database

This NAnt task will restore a database backup onto the target server...

posted @ Thursday, April 21, 2005 2:52 PM | Feedback (0) | Filed Under [ SQL Server Stuff Software Development Database Builds ]

April CTP (aka Beta 3) Available Today

Now that I have a blog and the SQLTeam.com homepage to post on I wonder which is more appropriate for each thing I'm posting.  Anyway.  I posted a blurb on the April CTP which is really the long awaited Beta 3 of SQL Server 2005.

posted @ Monday, April 18, 2005 9:09 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Excel Import Ignores Data

I've been trying to import data from an Excel spreadsheet using DTS.  I've been having problems with sparsely populated columns.  Basically if the first eight rows are blank it ignores any other values in the following rows.  This is because the Excel driver is only examing the first eight rows to determine data types and such.  There's a registry entry you can change to force the driver to search the entire spreadsheet before making any data type determinations.  I've got mine set to “0” which scans the entire spreadsheet.  I haven't had any performance problems and it's fixed a number...

posted @ Friday, February 04, 2005 9:55 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

Microsoft Developer Survey

We've been asked to post this by Microsoft: This is an invitation given by Microsoft to leading edge application developers to participate in a research survey that seeks feedback on improving the application development experience working with data platforms.  In the best interest of developers, the objectives of this survey are: To understand the existing, as well as upcoming challenges that has the potential of hindering optimal performance and productivity of those who develop applications and software components. To direct software manufacturers and its partners toward building powerful products that enable greater success among software development professionals. Determine the levels of priorities around...

posted @ Thursday, December 09, 2004 8:12 AM | Feedback (1) | Filed Under [ SQL Server Stuff ]

SQL Server 2005 Webcasts

Microsoft is doing a series of webcasts starting on December 6th, 2004.  These are focusing on developer related topics.  Definitely worth a look.  And did I mention they're giving away XBOX's?

posted @ Monday, October 25, 2004 9:46 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Password Memorability

(Is Memorability really a word?)  Anil John has an interesting post on the effectiveness of remembering certain types of passwords.  He also discusses the strength of each type of password.

posted @ Thursday, October 21, 2004 10:01 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

Tech*Ed 2004 SQL Server PowerPoint Decks Available

You can get them here.

posted @ Thursday, June 10, 2004 8:12 AM | Feedback (4) | Filed Under [ SQL Server Stuff ]

Automatically Processing SQL Server Trace Files

Lately I've been working on a small utility to process SQL Server trace files.  If you use scripted traces to generate trace files there's no automated way to get these into a SQL Server table.  I wrote a little .NET utility to do it for me that I'm calling ClearTrace.  You can download it here.  Currently it's a command line utility and VERY beta.  In the future I plan to rewrite it as a service and add better error checking.  ClearTrace should run on an admin workstation and not on the server.  It watches trace files based on a filter...

posted @ Sunday, April 11, 2004 7:31 PM | Feedback (3) | Filed Under [ SQL Server Stuff ]

Musing from the MVP Summit

The MVP Summit has been pretty interesting so far.  Much of what has been covered is under NDA and I'm not going to be the first to break it.  But I can tell you about the Summit itself.  Microsoft has 1,300 MVP's staying in downtown Seattle.  (And MANY of the MVP's look just like uber-geeks -- very easy to pick out in a crowd.)  There are about 75 SQL Server MVP's present out of roughly 100 total.  I've enjoyed attaching names to the faces from the newsgroups and various forums. The first day we spent on campus.  The SQL Server team...

posted @ Tuesday, April 06, 2004 9:20 PM | Feedback (4) | Filed Under [ SQL Server Stuff ]

MVP Summit

I'll be attending the Microsft MVP Summit in Seattle next week.  Hopefully I'll be able to pass on at least some of what I hear :)  I'm also hoping to hear a schedule for Beta 2.

posted @ Wednesday, March 31, 2004 3:57 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Tables Variables ARE Stored in tempdb

Well here's something I never knew.  When you declare a table variable it does get instantiated into tempdb.dbo.sysobjects.  I was under the impression it wouldn't.  If you run this code...declare @junk table(i int) select *from tempdb.dbo.sysobjectswhere name not like 'sys%' ... you can see it happening.  I found this and a few other interesting table variable nuggets at INF: Frequently Asked Questions - SQL Server 2000 - Table Variables.

posted @ Sunday, December 07, 2003 10:24 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Largest SQL Server Databases

Microsoft recently released a list of the largest SQL Server databases in production.  Topping the list is a 5 TB monster from Verizon Communications.  Actually Verizon appears 6 times on the top 24.  It seems they've made a pretty big commitment to SQL Server.  It scales down pretty quickly from the top though.  #24 on the list is “only” 512GB.  I guess if I want to win this award I'll have to get more data or drastically reduce my storage efficiency.  Hello images in the database!

posted @ Tuesday, December 02, 2003 2:11 PM | Feedback (7) | Filed Under [ SQL Server Stuff ]

306: Peeking Under the Hood: Advanced SQL Profiler

I'm speaking at PASS on Wednesday at 3PM.  For some unknown reason, I can't link directly to my presentation (PASS???).  You can visit the search page and list the presentations by time.  That's the easiest way I've found to get to it. My topic is Advanced SQL Server Profiler.  I'm going to cover a variety of topics related to Profiler.  I'll demo trace templates which are very handy if you do much tracing.  We'll cover some of the key events to monitor for performance.  These include CPU, locks and other events that can affect performance.  Next I'll discuss security related events...

posted @ Saturday, November 08, 2003 12:31 PM | Feedback (2) | Filed Under [ Events SQL Server Stuff ]

PDC Presentations Available

Many of the PDC presentations are now online. Most of the Yukon stuff is in the data track. There were some pretty interesting presentations. I especially enjoyed DAT401 - T-SQL Enhancements in SQL Server "Yukon". I'm just a T-SQL guy at heart!

posted @ Sunday, November 02, 2003 5:45 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Overview of .NET Programming Features in SQL Server "Yukon"

MSDN posted Overview of .NET Programming Features in SQL Server "Yukon".  The article has quite a few examples of CLR code inside SQL Server.  It gives good examples of how things will actually work.

posted @ Wednesday, October 29, 2003 2:39 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Microsoft SQL Server "Yukon" Website

Microsoft's Yukon web site is up.  They have links to articles and a demo video.  I haven't looked through in too much depth yet.

posted @ Wednesday, October 29, 2003 2:37 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Yukon Articles

Microsoft has posted a series of articles on Yukon. A Practical Guide to SQL Server Yukon Beta 1 DTS An Overview of SQL Server Yukon for the DBA Introduction to SQL Server Yukon Relational Engine Security Features Overview of Business Intelligence and Data Warehousing in SQL Server Yukon Reporting Services Deployment Guide

posted @ Tuesday, October 28, 2003 6:38 AM | Feedback (2) | Filed Under [ SQL Server Stuff ]

DBCC CHECKIDENT weirdness

DBCC CHECKIDENT is quirky.  If you run it to reset an identity column such as DBCC CHECKIDENT (jobs, RESEED, 30) it acts differently depending on whether you've ever had data in the table.  Books Online says The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. I have a script that does a pretty extensive data load.  In order to rerun it, I have a series of...

posted @ Tuesday, October 21, 2003 10:04 AM | Feedback (7) | Filed Under [ SQL Server Stuff ]

Reporting Services Beta

You can sign up to participate in the Reporting Services beta here.  I don't know if they take everyone or not but it can't hurt to sign up.

posted @ Thursday, October 16, 2003 2:44 PM | Feedback (1) | Filed Under [ SQL Server Stuff ]

Yukon Beta 2 in May 2004

MS announced at their partner event  that Beta 2 for Yukon will begin in May 2004.

posted @ Tuesday, October 14, 2003 6:38 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Kansas City SQL Server Users Group (Oct 9th, 2003)

Just a quick reminder that the Kansas City SQL Server Users Group will be having their meeting on October 9th, 2003 at 6PM.  I (Bill Graziano) will be presenting on Advanced Profiler topics.  This is an early version of the presentation I'm going to present at PASS in November.  The meeting is at Johnson County Community College in room 318 of the Carlsen Center.  Hope to see a few of you there. If you know of any other SQL Server users groups, please let me know and we'll post the information.

posted @ Tuesday, October 07, 2003 4:47 PM | Feedback (1) | Filed Under [ Events SQL Server Stuff ]

SQL Server Developer Edition really is under $50

Microsoft announced that as of August 1st, 2003 SQL Server Developer Edition would cost $49.  On that date I couldn't find anyone offering it at that price.  I recently did a quick search and found that at least Amazon is offering at that price.  I'm assuming most other vendors are also.

posted @ Wednesday, October 01, 2003 2:16 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Testing Triggers

I hate testing triggers.  It's a pain to figure out what's going on inside the code.  Especially when you're dealing with the inserted and deleted tables.  There are two great tricks I always forget.  The first is the SQL Debugger built into Query Analyzer.  Create a stored procedure to make the database change that fires the trigger.  Call this procedure in the debugger and it will step through the procedure AND the trigger(s).  The second trick is that Profiler can show you each statement inside a stored procedure that executes.  And this also shows each statement that executes inside a...

posted @ Monday, September 29, 2003 11:04 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Always use SCOPE_IDENTITY()

As I make changes to an application I'm reminded how ugly @@IDENTITY is.  We're using triggers to add some rudimentary data synchronization to this application.  The application inserts a record, we capture it in the trigger and go put a copy in the new table.  Except that the trigger sets a new value for @@IDENTITY when it fires.  So our quick changes that weren't going to affect anything are now much bigger than we planned.  Rather than doing this all at the database level we now have to open up the application and touch that code too.  Or find a...

posted @ Friday, September 26, 2003 9:46 AM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Powered by:
Powered By Subtext Powered By ASP.NET