Search results matching tag 'Transact-SQL Programming'

Learn More About SQL Server IO and Query Tuning in These Webcasts

KKline — Fri, 14 Dec 2012 18:50:00 GMT

I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.

SSDs are a Game Changer for SQL Server Storage

No, session is not exclusively about SSDs. But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks. As Bob Dylan said "Times, they are a'changin'". This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology. I'll show you how SQL Server handles storage structures, how to identify IO activity on Windows and SQL Server, and best practices for minimizing IO bottlenecks. Register now for: Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues.

Write Better SQL Queries

The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand (Twitter | Blog) and SQLCruise Impresario & Microsoft MVP Tim Ford (Twitter | Blog) as we take you through the query tuning process, discussing important DMVs to use during query tuning, as well as demonstrating several essential query tuning techniques that every SQL developer should know. Not only are we presenting an hour of top quality technical content, we’ll also be giving away some cool prizes, including the grand prize of a paid registration for the upcoming SQLCruise Miami, a $1,395 value! Register now for: SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford

I hope to see you at both of these sessions next week! Best regards,

-Kev

-Follow me on Twitter!

Help Me Update the History of SQL Server

KKline — Wed, 12 Sep 2012 15:01:00 GMT

I was chatting with my buddy, Buck Woody (Twitter | Blog), about a week ago and we were discussing that it's pretty hard to put together the entire history of SQL Server. Then the thought hit me that I'd already done this, to a degree, in my book Transact-SQL Programming. This was the first Transact-SQL programming book on the market way back in the SQL Server 7.0 days and even included full coverage of both Microsoft and Sybase variants of T-SQL.

The thing is, I was never able to put out a second edition due to some legal and contractual issues. So, help me catch up on the history of SQL Server:

The Ancient History of Microsoft SQL Server

SQL Server, like many things related to SQL, is a story of diversity. At one time, both Sybase and Microsoft had virtually the same product with Sybase's product targeted at Unix and enterprise environments while Microsoft targeted only the Windows enterprise. Today, the two products are entirely divergent. The code base for each product is unique to each platform and shares no code at all. For a while, both Sybase and Microsoft called their flagship database product "SQL Server", but now, Sybase calls their implementation of the product Sybase Adaptive Server Enterprise. So while the two products are now completely distinct, they continue to share a strongly similar Transact-SQL implementation. Other less noticeable similarities persist, such as the use of Tabular Data Stream (TDS), tempdb architecture, and a few other odds and ends.

Perhaps you want to know more about how things got to be the way they are? By reading this section, you will be making an admission that many programmers and developers are loath to - history is interesting ... perhaps even enjoyable. This is not actually a sign of weakness as some might attest. With tongue planted firmly in cheek, please read on. Table 1-1 shows the evolution of both Microsoft and Sybase’s version of SQL Server’s.

Notice that I stop at more than a decade ago. Help me add the subsequent details by leaving a comment! Extra points if you know the code name for each release.

1987 - Microsoft Buddies Up with Sybase

Microsoft and Sybase announce a technology and marketing partnership. Microsoft gets exclusive rights to market Sybase’s DataServer product on OS/2 and all Microsoft-developed operating systems. Sybase gets royalties and added credibility in the Unix and VMS markets. (Remember VMS?) Sybase ships their first commercial DBMS product, called DataServer, for Sun workstations running Unix.

It was only 20 years prior that the first Super Bowl was played as the Green Bay Packers of the National Football League defeated the Kansas City Chiefs of the American Football League, 35-10. (Hey, now this is important!)

1988 - Microsoft Buddies Up with Ashton-Tate

Microsoft and Ashton-Tate announce a marketing partnership. Microsoft can’t put a dent in dBase’s tremendous market presence. Ashton-Tate wants access to Sybase’s powerful multi-user database management technology, which ships this year. Microsoft forms a three-way alliance.

Another interesting three-way alliance was also making headlines at that time, as independent counsel Lawrence E. Walsh probed the Iran-Contra affair.

1989 - SQL Server v1.0 Ships

Ashton-Tate/Microsoft SQL Server version 1.0 ships.

Perhaps not coincidentally, NYC transit fare rises from $1.00 to $1.15.

1990 - Microsoft Ships SQL Server v.1. for, OMG, Windows!

Ashton-Tate dBase IV was floundering. Microsoft wanted to beef up its offerings for the new OS/2 LAN Manager product. Microsoft and Ashton-Tate quit the partnership. “Microsoft SQL Server” version 1.1 ships by summer with support for Windows 3.0

Another partnership came to an end that year: ousted Panamanian leader Manuel Noriega surrendered to U.S. forces, after taking refuge in the Vatican's diplomatic mission.

1991 - Microsoft Goes All-In on Windows 3.0

A proliferation of Windows 3.0 front-end tools spurs the growth of Microsoft SQL Server. Later that year Microsoft and Sybase amend their contract to allow Microsoft to make actual bug fixes - all under Sybase supervision. IBM and Microsoft call off their OS/2 partnership with Microsoft to focus on Windows. Sybase surpasses the $100 million revenue mark and goes public on NASDAQ.

Almost one thousand years ago to the day, significant improvements in front-end tools (i.e. the plow and horse yolk) spur a huge population boom in Europe. The Vikings and Europeans call off their partnership, with the Vikings dragging off most of the loot. Meanwhile, Islam is continuing its military expansion throughout the world. Fortunately, medieval Europe does not have to endure lengthy beta programs, although computer geeks have to endure a plague of viruses. No fair!

1992 - Microsoft Goes Nuts on Windows NT

Microsoft and Sybase SQL Server 4.2 ships. Microsoft diverts its attention away from OS/2 and into developing Windows NT. Microsoft SQL Server for Windows NT later ships in beta release while Sybase ships its much-vaunted System 10.

A famous diversion of attention in the political arena, you ask? Then President George H. W. Bush lost his lunch during a state dinner in Tokyo; White House officials tried to deflect attention saying Bush was suffering from stomach flu.

1993 - Microsoft and Sybase Become Unfriends

Microsoft Windows NT 3.1 ships, closely followed by Microsoft SQL Server. By 1994, Sybase SQL Server System 10 and Microsoft SQL Server were competing unabashedly as the two formally ended their partnership. (This is about the time that I started to use Microsoft SQL Server 4.21 on OS/2).

Likewise, the American voting public formally ended their relationship with “Read my lips” President George H. W. Bush.

1995 - Microsoft Enters the Enterprise Computing World

Microsoft SQL Server 6.0, a very significant release, ships in June. Up until this time, most Microsoft products were considered departmental-level only. SQL Server also begins to push out many PC-level database products, with the noticeable exception of Microsoft Access. Access will be a thorn in the side of SQL Server DBAs until ... uh ... probably 10+ years from now.

Other significant release for 1995 included O.J. Simpson - not guilty.

1996 - SQL Server 6.5 Goes Into Production

Though released in April, it’s no April Fool’s joke. SQL Server 6.5 is the most powerful and capable version Microsoft had ever produced. It also received considerable press because of its innovative user interface and free tools.

1997 - Microsoft SQL Server 7.0 Beta Released. Remember the Code Name?

Is there a causal relationship between the Monkees' appearance at the Hollywood Bowl (June 10, 1967) and the first shipment of the Apple II Computer (June 10, 1977) and the release of MS SQL Server 7.0 beta (June 10, 1997)? It does make you wonder, doesn’t it?

1998 - Microsoft SQL Server 7.0 RTM

Microsoft offers another set of new innovations in the SQL Server 7.0 release, including an "all-in-the-box" strategy. Other major DBMS vendors had, and still have, separate products for ETL (extract-transform-load), administration tools, scheduling, and business intelligence. Not Microsoft. With SQL Server 7.0, Microsoft included the Data Transformation Service (DTS) for ETL work, Query Analyzer and other administration tools, OLAP services (my brain gets a little cloudy on this one), along with the already existing scheduler, SQL Agent.

2000 - Microsoft SQL Server 2000

Branding experts said that version numbers should get too big, or else customers would perceive the software as stodgy and old fashioned. So Microsoft switched from the version number nomenclature to the one highlighting the year of the release for SQL Server 2000. This release included the game-changing Enterprise Manager (EM). Forced many other DBMS companies to at least attempt to build useful tools (anyone use SQL*Plus from Oracle). Again, my brain is getting dusty with old age and too many releases, but IIRC this was the release in which Microsoft finally got ride of page-level locking in favor of row-level locking. That shuts up a lot of Oracle-bigots, but doesn't truly ease their smarmy attitude. Grudge matches continue unabated.

I'm going to follow-up with another blog post carrying forward with SQL Server 2000, including highlights for important half-steps like SQL Server 2000 SP3, the "Slammer" security release.

Feel free to throw down some of your SQL Server trivia knowledge about release and features for releases. Share a picture of one of your old boxes, if you have one!

Enjoy,

-Kev

Twitter | Facebook | YouTube | LinkedIn | Blog

Flexibility When Waiting on Locks

KKline — Thu, 17 May 2012 13:29:00 GMT

Speaking at a recent SQL Saturday, an attendee in one of my sessions wanted to know how they could more flexibly react to locks on their application than to wait for blocks to occur and then kill the SPID at the head of the blocking chain. They were also interested in some alternatives to using the SQL Server syntax like the WITH (NOLOCK) hint, since that might have unintended consequences due to allowing reads on uncommitted data.

One alternative I suggested is the SET LOCK_TIMEOUT n statement. Since most of the attendees hadn't heard of this statement, I figured it'd make a good blog post.

When using the statement, you can set this context for the connection, for a batch of code (such as a function or stored procedure), or for a single SQL statement (excluding a few DDL statements such as CREATE/ALTER DATABASE). By passing a numeric value with the set statement, you specify the number of milliseconds that the statement will wait for a lock to be released before returning a locking error. 0 means don't wait at all and -1, the default, means wait forever. Once changed, the new setting stays in effect for the remainder of the connection. So you might want to set it back to the default if you want it to apply to only one statement, say a SELECT, in a big batch of statements.

You can also get the same behavior by using the READPAST locking hint.

Hope this helps with those troublesome locking situations! Enjoy,

-Kev

-Follow me on Twitter

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

KKline — Fri, 16 Mar 2012 14:30:00 GMT

Here's an evergreen question. It's a question that never completely goes away. But lately, I've been getting it a few times per week. So I thought it's time to readdress the question, which usually takes some form of the following:

I can't really do effective development on my little dev laptop because our production SQL Server database is 15 gazillionbytes, way too big for my workstation. What's a uber-nerd to do?

Well, maybe they didn't use the word "uber-nerd". But you get my drift, right? The production database is really, really big - unmanageably big for keeping a local copy. So that means the dev either has to create a metadata-only version of the database, which won't produce realistic query plans, or somehow crush their laptop under 15 gazillionbytes of MDF and LDF files.

Actually, you have a better alternative - a clone database, sometimes called a shell database. Here's how I described a cloned database a few years ago here in my Tool Time column for SQL Server Pro Magazine:

In effect, a cloned database includes all of the schema objects of the database (e.g., tables, views, stored procedures), as well as the statistics and histograms (the so-called "statistics blob"). This metadata is quite small by volume but can tell you what estimated query plans look like outside of a large production environment and how those estimated query plans might change when SQL Server is upgraded. Cloned databases are especially useful when the data is confidential, classified, or subject to privacy laws.

The article gives you all the detail you need to effectively and quickly create a small version of a big, ol' production database that produces the same query execution plans as you'd get on the prod server.

If you're struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! You'll be glad you did.

Enjoy!

-Kev

-Follow me on Twitter

Everybody Needs a Test Harness

KKline — Mon, 31 Oct 2011 14:17:00 GMT

When you're developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?

I know that I did just that, for years. It wasn't until I was trying to performance tune some existing code that I realized I hadn't actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn't checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That's easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn't need to clear the caches) and adds a metric or two for good measure - literally - so we can better measure what's happening in that code.

Here's what my test harness looks like:

/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */

/* Flush dirty pages from the buffer to the database files. */

CHECKPOINT;

/* Flush the data cache and procedure cache, respectively. For DEV environments only! */

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

-- Whatever SQL code you'd like to process goes below.

SELECT SalesOrderID

FROM Sales.SalesOrderHeader H

WHERE CustomerID = 344

GO

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

/* Textual Execution Plans, if desired.

SET SHOWPLAN_TEXT ON;

SET SHOWPLAN_TEXT OFF;

*/

I also like to include the execution plans a lot of the time. You might wonder why I don't save the execution plans for the GUI in SSMS? Well, I'm a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin' smarter, not harder, Baby!

So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!

Thanks,

-Kevin

-Follow me on Twitter