Search results matching tags 'SQL Server 2012' and 'Tips'

Squishy Limits in SQL Server Express Edition

KKline — Thu, 28 Mar 2013 12:19:00 GMT

It's an old story you've probably heard before. Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of cannibalizing sales of your commercial products. Microsoft has take this strategy with SQL Server Express Edition, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications.

One such limitation of SQL Server Express Edition is that it supports no more than 1GB of RAM for the instance. Of course, you could have many Express Edition instances on a single Windows server, each with its own 1GB of RAM.

But what does that metric of 1GB of RAM actually mean? The key thing to remember is that the restriction is for buffer cache. Since SQL Server has many other caches, even when not counting the plan cache, there are plenty of other caches within SQL Server. (Run a query against sys.dm_os_memory_clerks if you'd like to see some of the others). Because only the buffer cache has the strict 1GB limitation, you can actually watch SQL Server Express Edition's memory working set size grow to around 1.4-1.5GB due to the other memory caches at play.

Pawel Potasinski, a SQL Server MVP from Poland (Twitter | Blog), once posted an interesting repro for this behavior:

-- Assess amount of databases resident in buffer cache

SELECT
 CASE
 WHEN database_id = 32767 THEN 'mssqlsystemresource'
 ELSE DB_NAME(database_id)
 END AS [Database],
 CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB in buffer cache] 
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id 
ORDER BY 2 DESC; 
GO

-- Assess amount of tables resident in buffer cache
SELECT
 QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
 QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
 CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB In buffer cache] 
FROM sys.dm_os_buffer_descriptors AS d 
 INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id 
 INNER JOIN sys.partitions AS p ON (u.type IN (1,3) AND u.container_id = p.hobt_id) OR (u.type = 2 AND u.container_id = p.partition_id) 
WHERE d.database_id = DB_ID() 
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY [Object] DESC;
GO

-- Fill up Express Edition's buffer allocation
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
 DROP TABLE dbo.test;
GO

CREATE TABLE dbo.test (col_a char(8000));
GO

INSERT INTO dbo.test (col_a)
 SELECT REPLICATE('col_a', 8000)
 FROM sys.all_objects 
 WHERE is_ms_shipped = 1;

CHECKPOINT; 
GO 100

The bottom line for the hard memory limit of SQL Server Express Edition is "Yes, it's limited. But it's a squishy limit. Not a hard limit."

Although your mileage may vary, I'd bet a dollar that you'll find more than 1GB in the active working set for your instance of SQL Server Express Edition. I am curious, however, if you're seeing much variation between versions and even service packs of SQL Server? Let me know if you try this out on more than one version and/or service pack level of SQL Server. Did it change much between versions? Let me know!

Enjoy,

-Kevin

-Follow me on Twitter!

Google Author

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!

Quick Tip - Speed a Slow Restore from the Transaction Log

KKline — Wed, 14 Nov 2012 15:59:00 GMT

Here's a quick tip for you:

During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.

If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. You may also wish to keep an eye on LOGBUFFER wait stats.

I'd love to hear your feedback. Have you tried this technique? Did it work as advertised? Did it require some changes to work on a specific version or edition?

Many thanks,

-Kev

-Follow me on Twitter!

Two New Slide Decks. Plus, the Week in Colorado.

KKline — Mon, 20 Aug 2012 15:03:00 GMT

I had the honor of traveling the great state of Colorado last week, speaking at the PASS chapters in Boulder, Colorado Springs, and Denver. At all three events, we had a stellar attendance and, at least in Denver, broke all the records in recent memory both in terms of overall attendance and in first-timers. Denver, in fact, was standing room only and had nearly 30 first time attendees. Great news! I also want to give a special shout-out of thanks and appreciation to Chris Shaw (Twitter | Blog) whose hard work and tenacity ensured that all of Colorado got to see me speak. From left to right, Gabriel Villa (Twitter), me, Chris Shaw, and Rebecca Mitchell (Twitter). If it weren't for Chris, I wouldn't have been there. Thanks for putting in the time, amigo!

New Slide Decks!

During the 3-day jaunt, I presented two of my more popular sessions. These are updated slide decks, in case you want to download them here:

UG - End-to-End Troubleshooting

and

UG - Top 10 SQL Server Administration Mistakes

Be sure to check in the Slides area of the website, if you want to see the links for SpeakerRate, and in the case of several of my presentations, white papers, video recordings, etc. It's the People that Matter

A Blast from the SQLPASS Past!

I've always tried to maintain the relationships I built with the founding members of the PASS board of directors. After their time on the PASS board, almost all of them have moved on from SQL Server to other adventures. Pam Smith, the first president of the organization, is now a professor. Guy Brown, the second president, is now the director of IT at his same employer, rather than just SQL Server as when he was on the PASS board. A few, such as Kurt Windisch, a former VP of PASS, and my good friend Joe Webb (Twitter | Blog), are still active in the SQL Server space. One relationship that I've enjoyed over the years is with PASS' original Microsoft liaison and now a Denver-area software entrepreneur Steve Murchie (at right) running his own healthcare IT outfit. Steve has been a source of inspiration to me and also of great advice for all things startup-related. It was great to connect with Steve and catch up on his latest doings.

I also got to enjoy an evening out with the local attendees after the Denver user group meeting. It was great to hang out with folks there. I got to meet Kevin Cox (twitter), a member of Microsoft's incredibly talented SQLCAT group, and for whom I was a technical editor on a SQL Server v6.5 book back in the Neanderthal era. That shows just how old both Kevin and I actually are. Other cool folks that I got to meet included Steve Wake, Mike Fal, Marc Beacom, Jason Kassay, Jason Horner and my ol' buddy, Kelly the Greeley Geek.

On top of that, long-time SQL Server MVP and all-around awesome guy Steve Jones (Twitter | Blog) visited. I kept him out way too late that night which, to be honest, isn't usually my style. But the good conversation propelled us on past midnight.

The Good Folks at SQL Server Professional and Windows IT Professional Magazines

I've written for SQL Server Professional (formerly the artist known as "SQLMag") in some form or another starting from my first cover article for them way back in the mid 1990's. My Tool Time column has been going strong there since, oh, around 2006 iirc. For most of the time I've known the folks at SQLMag, they were located in Loveland, CO but they were able to move to some incredibly nice digs just up the road in Fort Collins. In all the many years, I've written for them, I'd never been to their offices - until now. It was great to visit and break bread with Megan (to my right), Blair (across), and Jaylee (across and to my right)!

I've always supported SQLMag and encourage you to subscribe. On top of the goodness already in the digital magazine, there are some neat developments coming down the pipeline with SQLMag which I think we'll all enjoy. Be sure to subscribe today! (You can click the badge on the left or simply go to http://www.sqlmag.com).

What's Next?

My current travel schedule is pinned up on this blog post HERE. However, I also know of a couple on-line appearances and probably a trip between the long gap between now and my next in-person appearance at the Orlando SQL Saturday at the end of September, where I'll also be teaching a pre-conference seminar (register HERE for the seminar).

SSWUG

The first on-line event to note is my a presentation by SSWUG of my Leadership Skills for IT Professionals video series, starting on August 24th. Sign up using the hyperlink (note that a video plays immediately upon loading the webpage, in case you want to be ready to pause or stop it). You can also buy a DVD set of the 14 hours of leadership training content.

24HOP - The 24 Hours of PASS Event

I'll also be speaking on the topic of influence in the next 24 Hours of PASS coming up on September 20th and 21st. Registration for the twenty-four hours of around the clock presentations is completely free and well worth your time. Check the schedule for the event and register! Even if you can only watch one or two sessions (or even zero sessions), be sure to register so that you'll automatically be notified when the sessions become available as streaming media.

Enjoy!

-Kev

-Follow me on Twitter, LinkedIn, and Facebook

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