Search results matching tags 'Internals' and 'Administration'

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!

Microsoft Document Watch for Operational Excellence

KKline — Mon, 08 Aug 2011 19:59:00 GMT

Back when my day-to-day duties included database administration work and enterprise architecture, I became rather obsessed with the idea of operational excellence. I read everything I could on the topic. I made a list of favorites, which became somewhat shabby over time, as I dog-eared important pages and scribbled notes in the margins. (Perhaps that list of favorites might, in and of itself, make a good blog post). Fast-forward a decade and I'm still mightily interested in operational excellence for IT organizations. It's just that so much good material is available for free on the web. Here's a run-down of several useful documents and downloads to improve overall operation performance for those of you in a Microsoft-centric IT organization:

Microsoft Operations Framework

Microsoft Operations Framework (MOF) version 4.0 guide is practical guidance for IT organizations. With the release of version 4.0, MOF now reflects a single, comprehensive IT service lifecycle—it helps IT professionals connect service management principles to everyday IT tasks and activities and ensures alignment between IT and the business.

Infrastructure Planning and Design

The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.

Microsoft Baseline Security Analyzer 2.2 (for IT Professionals)

The Microsoft Baseline Security Analyzer provides a streamlined method to identify missing security updates and common security misconfigurations. MBSA 2.2 is a minor upgrade correct minor issues and add optional catalog support.

Security Compliance Manager

The Microsoft Security Compliance Manager provides centralized security baseline management features, a baseline portfolio, customization capabilities, and security baseline export flexibility to accelerate your organization’s ability to efficiently manage the security and compliance process for the most widely used Microsoft technologies.

New on SQLMag.Com: Update to SP_WHOISACTIVE

KKline — Thu, 21 Apr 2011 14:12:00 GMT

I profiled Adam Machanic's (blog | twitter) excellent stored procedure, SP_WHOISACTIVE, back in August of 2010 in my monthly SQLMag column, Tool Time. Adam has been diligent about maintaining the tool and adding new features. Read the details on my SQLMag Tool Time column (here - http://www.sqlmag.com/blogs/tool-time/entryid/76341/new-release-of-sp_whoisactive).

Enjoy!

-Kev

Twitter at kekline

How Do You SKU?

KKline — Wed, 30 Mar 2011 14:49:00 GMT

I’d like your opinion here.

Follow my logic here for a moment as I walk through a couple rhetorical questions. Have you ever had a friend developed an application entirely on SQL Server Developer Edition? (Not that YOU would ever do such a thing, but maybe you know someone who has. Right?) And has your friend’s IT department actually deployed said application only to discover that they’re only licensed for Standard Edition in their production environment? And then was your friend’s IT management team is horrified to learn that they’ve either got to go through the very expensive process of extracting all of the Enterprise and/or Datacenter Edition features for the production application in order to remain in compliance, upgrade to the more expensive SKU licenses, or risk a potential future audit?

I’m not saying that this has happened to any of us. We’re too smart for that, after all. But have you ever known anyone who’s had this experience?

Having worked with a lot of customers another commercial RDBMS platforms (which I’ll euphemistically call “SEER” from Redforest City and “IB4” from Upstate City), I can tell you that auditing is a fun and exciting way for those platform vendors to make a LOT of money. This is especially true because a production application, once successfully deployed, tends to be too valuable to disable or otherwise compromise because high-end features slipped in to the development cycle even though the production environment only a “standard edition” SKU in place. Ouch! Talk about being caught between a rock and a hard place. Now, keep in mind that this is a strategy used by SEER and not by Microsoft. But Microsoft could implement the same sort of licensing audits if they wanted to. (Please leave a comment here if you have ever been audited. I’d love to hear your experiences, at least as much as NDA’s allow).

So if you use SQL Server Developer Edition (DE), of any version, would you like to see a feature that enables you to run DE not in its default “full featured mode” but at another SKU level, such as good ol’ Standard Edition? I know I would.

If you’re on the same page as I am, there are a number of suggestions logged on Connect about this very feature! Make your voice heard! Check out:

https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

Of course, the more skeptical reader might say “Hey, that’s their tough luck. Developers should know the difference in the SKU licensing options and feature sets of whatever SKU they’re developing on compared to what they’ll deploy on.” And I wouldn’t fault you for saying so.

But I would go on to point out that much of Microsoft’s success in enterprise IT settings can be traced back to their very strong relationship with developers. And anything that Microsoft can do to empower developers to save time, money, and resources during the development phase of an IT project in turn energizes that relationship between developer and Microsoft.

It also makes the life of the DBA that much easier, because they don’t need to imply that those cowboys on the development team went off half-cocked again. So what’s your opinion? Should SQL Server Developer Edition include a feature that sets the SKU-level of the database engine?

Back in Atlanta! Wed, Feb 9 2011

KKline — Mon, 07 Feb 2011 17:04:00 GMT

I always enjoy spending time with my friends from Atlanta, as well as meeting folks and making new friends. If you live in the Atlanta area, I hope you'll join me on the evening of Wednesday, February 9th, 2011. Details are at the Atlanta SQL Server user group website.

It's common knowledge that I have a terrible memory for many things. However, one of the few things that my memory is usually really good at is remember names & faces (and remembering stories, but that is another story as well).

It's only in the last couple years that I've gotten to know Atlanta-area folks like Aaron Nelson (blog | twitter), at left. Aaron likes to tease me because the first time or two we talked after our initial introduction, I kept getting his name wrong. That's uncharacteristic of me. But, hey, I was distracted because Aaron had brought his 12 year old daughter along and I kept wishing that I'd brought mine!

Aaron has really good content on SQL Server and PowerShell on his blog and in his presentations. Don't miss out!

Another person in the Atlanta area I've enjoyed getting to know better is Stuart Ainsworth (blog | twitter). Stuart has really stepped up to the plate these last couple years as a leader and a volunteer. I'm really impressed with his efforts, especially because they're in very important but rather invisible and thankless tasks. One example is his work in succession governance for PASS. Stuart, I salute you and thank you for your efforts. I hope to see y'all there! And if I don't, y'all come up and visit us in Nashville some time.

Cheers,

-Kev

Twitter at kekline

New on KEK.com - Presentation: SQL Server for Oracle DBAs

KKline — Mon, 02 Aug 2010 12:23:00 GMT

Back in late May, my pal Buck Woody (blog | twitter) and I presented SQL Server for the Oracle DBA. Buck played Jerry Lewis' role, while I played the straight man a la Dean Martin. You can see the recording and slide deck here. Since both Buck and I spent a considerable number of years working on Oracle, we felt like we had pretty good legs to stand on doing this presentation. Here's the abstract:

In enterprise IT, database professionals face a mixed-bag of platforms within their environments -- and the overlapping duties that go along with them. Whether you like it or not, you get asked to support Microsoft SQL Server operations even though you're an Oracle DBA.

In this instructive webcast, experts from Microsoft and Quest with years of experience in both platforms will discuss the biggest issues and areas of interest for Oracle DBAs who need to work on Microsoft SQL Server. You'll learn their tips and tricks for efficiency and gain a deeper understanding of SQL Server to help you add more value than ever to your organization.

Of course, there are always questions and we aim to please. Here are the transcripts of the Q&A chat session (below) that accompanies the presentation. The only editing I did was ... [READ MORE]

Two Free Training Webcasts Open for Registration

KKline — Mon, 24 May 2010 20:59:00 GMT

We've got two sessions that you need to sign up for right away. The upcoming webcast for Oracle-oriented folks has huge registration numbers. So get in while you still can before we hit the limit of what LiveMeeting can handle.

Pain of the Week: SQL Server for the Oracle DBA

Webcast: SQL Server for the Oracle DBA
Date: Thursday, May 27, 2010 (Just a couple days hence!)
Time: 8 a.m. Pacific / 11 a.m. Eastern / 4 p.m. United Kingdom / 5 p.m. Central Europe
Duration: 45-60 minutes
Cost: FREE

In enterprise IT, database professionals face a mixed-bag of platforms within their environments -- and the overlapping duties that go along with them. Whether you like it or not, you get asked to support Microsoft SQL Server operations even though you're an Oracle DBA.

In this instructive webcast, experts from Microsoft and Quest with years of experience in both platforms will discuss the biggest issues and areas of interest for Oracle DBAs who need to work on Microsoft SQL Server. You'll learn their tips and tricks for efficiency and gain a deeper understanding of SQL Server to help you add more value than ever to your organization.

Presenters:
Kevin Kline, SQL Server MVP, Quest Software
Buck Woody, Senior Technology Specialist, SQL Server, Microsoft

Pain of the Week: Easy Ways to Fix Hard SQL Server Problems - Without a Guru

Webcast: Easy Ways to Fix Hard SQL Server Problems – Without a Guru
Date: Thursday, June 17, 2010
Time: 8 a.m. Pacific / 11 a.m. Eastern / 4 p.m. United Kingdom / 5 p.m. Central Europe
Duration: 45-60 minutes
Cost: FREE

In this instructive webcast, you’ll learn straightforward methods for troubleshooting and resolving common poorly performing stored procedures and queries in SQL Server 2008. The presenters will show you how to easily:

Read query execution plans and identify bottlenecks in performance
Record system performance metrics for trend analysis
Design databases and write Transact–SQL code to avoid common problems
Understand what an index is, what it does, and how to build one
Use industry-accepted best practices and develop your own to arrive at optimal database performance

Much of the webcast is based on Grant’s book, "SQL Server 2008 Query Performance Tuning Distilled."

Don’t miss this chance to get practical advice from working experts instead of just theories from academics!

Presenters:
Kevin Kline, SQL Server MVP, Quest Software
Grant Fritchey, “Scary” DBA from FM Global

Join Me May 19th for 24 Hours of PASS

KKline — Mon, 26 Apr 2010 14:16:00 GMT

Join Us for 24 Hours of SQL Server Training

(with a special focus on SQL Server 2008 R2)

Did I mention that your Cohorts in Crime (that be me and Brent Ozar) are presenting?!?

The FREE 24 Hours of PASS event is bringing an exceptional lineup of SQL Server and BI experts to your computer starting at 12:00 GMT (UTC) on May 19.

Get an in-depth look at the hottest SQL Server and BI topics, including (but not limited to!) - the new SQL Server 2008 R2, with its business intelligence and data management innovations, and much more.

When does it start? 12:00 GMT (UTC):

New York: 08:00
Chicago: 07:00
San Francisco: 05:00
London: 13:00
Paris: 14:00
Moscow: 16:00
Mumbai: 17:30
Singapore: 20:00
Sydney: 22:00

The roster of phenomenal speakers features many MVPs and top-rated presenters, including Adam Machanic, Andy Leonard, Brad McGehee, Brent Ozar, Brian Knight, Chuck Heinzelman, Dean Richards, Don Kiely, Don Vilen, Donald Farmer, Glenn Berry, Greg Low, Jacob Sebastian, Jessica Moss, Kevin Cox, Kevin Kline, Louis Davidson, Maciej Pilecki, Peter Myers, Peter Ward, Rushabh Mehta, Sean McCown, Simon Sabin, Thomas Grosher.

Check out all the great SQL Server sessions you can attend for FREE. Share this information with a friend or colleague.

PASS is looking forward to having you join us all for this exceptional event. Please contact us at 24hrs@sqlpass.org with any questions. You can also find lots of general details at http://www.sqlpass.org/24hours/2010/.

April Omnibus

KKline — Tue, 20 Apr 2010 15:20:00 GMT

I freely admit it - I'm a sluggard. I should be blogging a couple times per week and tweeting in between. But, for some unknown reason, April has been a tough month to get this in gear. Hence, I'm putting out an omnibus post to cover all of the stuff I've been up to, instead of the one-off's I usually post when I've got something new to mention.

Isn't it funny how life gets in the way of the stuff we want and intend to do? As they say - "The road to hell is paved with good intentions", or was that Detroit? But I digress.

A New Translation!

I don't like to toot my own horn. [sarcasm on] You can tell that I'm completely honest when I say that since I have a couple blogs and a twitter account. [sarcasm off] But one thing I'm really proud of is how popular SQL in a Nutshell is. It's now translated into approximately eight languages, with Russian being the newest language:

I'm even more proud of my lil' girl than that book!

I also had the pleasure of working with another translator and I'm looking forward to having a copy of the book in simplified Chinese soon too!

Lots of Speaking Events Coming!

There will be more details on my Events page soon, but here's the run down on what's just around the corner:

May 4; recording a spot for TechNet Radio
May 13-14; Louisville, KY; Stored Procedure Best Practices
May 20; 24 Hours of PASS; Top 10 Mistakes on SQL Server
May 22; Indianapolis, IN; SQL Server Internals & Architecture
June 7-11; Microsoft TechEd Conference in New Orleans, LA; Birds of a Feather and Ask The Experts
June 23; Knoxville, TN; ditto the Indy event
Aug 5-7; DevLink in Nashville, TN; several sessions throughout the conference

I also have three "Pain of the Week" webcasts schedule with Quest over the next couple months, with great co-hosts like Buck Woody and Grant Fritchey. The sessions aren't officially scheduled yet, but you can find them here.

Also, a big thanks to the local PASS chapter in Houston for the warm welcome they put out when I spoke there on April 12 and to the fine folks in Chicago for their awesome SQL Saturday on April 17th.

New Articles and Session Recordings

I had a couple new articles come out last month which I either wrote or added commentary too, including Master Data Services could spur SQL Server 2008 R2 migrations appearing at SearchSQLServer.com.

The PASS DBA Virtual Chapter just posted a recording of my session on SQL Server Internals and Architecture.

I made an appearance on Microsoft's Thrive website as their IT Pro of the Month for March. I also had the opportunity to work with Steve Wynkoop at his SSWUG.org vConference in March.

And don't forget about the incredibly popular Quest vConference that Brent, Ari, and I did on troubleshooting and performance tuning using SQL Server DMV's. You can get the code samples, download the slides, and rate the presentations at http://questkb.com/live and/or http://www.vconferenceonline.com/shows/spring10/quest/conference/ondemand.asp