SQL Server Archives - Page 26 of 33

29Dec 2009 by Grant Fritchey 6 Comments

The Other Server

I had a fun support call I need to share. A developer called up to tell me that a particular dev instance was offline. He informed me that the server SQL08\DEV01 (the names have been changed to protect the innocent) server was completely inaccessible. I knew that multiple development teams would shortly be calling and that I'd better get on this issue most riki-tik. I quickly typed the connection string into Management Studio and watched in confusion as the server instance popped up on my screen. It was fine. I did a number of checks, looking for active connections, recent connections, errors in the log, indications of a recent reboot... Nothing. I called the developer back and told him that the server was fine. He called me again in two…

23Dec 2009 by Grant Fritchey 1 Comment

A Lack of Excitement

PASS, PowerShell, SQL Server, T-SQL, Tools

I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven't been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is. The fact of the matter is that I've been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I've blogged before about our use of Microsoft's Operations Manager for monitoring our servers and how we've built custom rules and monitors to keep an eye on things. I've also mentioned how…

08Dec 2009 by Grant Fritchey 4 Comments

T-SQL Tuesday #1: Date/Time Tricks

SQL Server, T-SQL

I'm going to try out Adam Machanic's idea for a blog party. The topic this month are Date/Time tricks. Instead of supplying a trick for Date/Time, I'm going to caution you about the tricks that you use. Let's takeÂ a simple issue. You want to pull back data from a table, let's use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let's create an index on the table: CREATE INDEX ixTest ON Production.TransactionHistoryArchive (TransactionDate) The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003: SELECT tha.TransactionID FROM Production.TransactionHistoryArchive AS tha WHERE DATEPART(yy,tha.TransactionDate) = 2003 AND DATEPART(mm,tha.TransactionDate) = 7 In theory you should be able to use the index that was created earlier,…

01Dec 2009 by Grant Fritchey 4 Comments

How do -You- use SQL Server

nHibernate, SQL Server, T-SQL, Tools, Visual Studio

I've been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That's a tough one. It would make a much shorter, and easier,Â blog post to describe the things we don't use it for. However, keeping with the spirit of these tags, I'll try to lay out it. For those that don't know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we'll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70's and 80's propped up by ancient men with pocket protectors, spit, bailing wire…

30Nov 2009 by Grant Fritchey 6 Comments

SQL Server XQuery For Idiots

SQL Server, T-SQL

I'm still struggling with learning XQuery. My latest little revelation was small, but vital. The difference between: @inXML.nodes('/rss/channel/item') and @inXML.nodes('/rss[1]/channel[1]/item') Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like "/rss/channel" so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data. It's a little thing, but it made an enormous difference. I've still got a long way to go in learning how to use XPath within XQuery. UPDATED: I modified the title so that it's…

28Oct 2009 by Grant Fritchey 6 Comments

Snags With Profiler GUI

SQL Server, T-SQL, Tools

Running the Profiler GUI against a production server is not something you should do. I've outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I'm doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it's called, on the same server, from SQL Server Management Studio. I needed to capture some trace events and do it quickly, so I decided to use the GUI, just this once. I put filters on it so that I would only collect certain data, and the database I was collecting…

21Oct 2009 by Grant Fritchey 1 Comment

Characters

SQL Server, T-SQL, Tools

No, I'm not talking about a Dickens novel. I'm talking about the number of characters in a string. I had a painful time recently because of the word "characters."Â If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It's a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV's has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I'm quoting directly from books online measure the "number of character" offset from the beginning of the SQL string and from the end of…

21Oct 2009 by Grant Fritchey No Comments

Free Training RIGHT NOW

SQL Server, T-SQL

Quest Connect 2009 is occurring even as I type this. Get on over there if you're interested in some free training. I recorded a session on understanding execution plans. But even better, there are live sessions with some great people. Stop reading this, click the link, get yourself some free training.

13Oct 2009 by Grant Fritchey 5 Comments

Table Variables Are Only in Memory: Fact or Myth

SQL Server, T-SQL

I keep seeing theseÂ conceptsÂ that have long been disproven, posted again and againÂ on newsgroups as if they were valid information. The latest? Table variables are better because they're only in memory where as temporary tables write to the disk throughÂ tempdb. This one is abjectly wrong. I'm not even going to hedge with "it depends." From a macro point of view, thereÂ are onlyÂ a fewÂ differencesÂ between temporary tables and table variables, statistics being the biggest. Temporary tables have 'em and table variables don't. Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size. Some of the minor differences, and why you might want to use table variables over temporary tables, table variables won't cause a statement recompile while temporary tables will, table…

05Oct 2009 by Grant Fritchey 15 Comments

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

SQL Server, T-SQL

Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: DECLARE @sql NVARCHAR(MAX), @value int; SET @value = 43668; SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh '; SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod '; SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID '; SET @sql = @sql + 'WHERE soh.SalesOrderID = ' + CAST(@value AS NVARCHAR); EXEC (@sql); And as ad…

Category: SQL Server