Search results matching tags 'Internals' 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!

Microsoft Windows Platforms Blog Watch

KKline — Wed, 20 Jul 2011 14:44:00 GMT

Remote Desktop Services Component Architecture Poster

Grab your own poster! A visual guide to key Remote Desktop Services technologies in Windows Server 2008R2

Virtually Free

Get the latest update rollup package for the Hyper-V role in Windows Server 2008 R2 and be sure to bookmark the Windows Virtualization Team Blog. Be sure to check out blog World Simplified is a Virtual World. And doncha evah neglect application virtualization, such as the goodness at the App-V Product Team Blog.

Let's Optimize Some Desktops (Assuming You Have Gone Full Cloud Yet)

Check out The Official Microsoft Desktop Optimization Pack (MDOP) Blog where you can get cool tools like the Diagnostics and Recovery Toolset (DART), currently in its v7 beta release.

We Can Always Perform a Little Better

Have you checked out the Ask the Performance Team blog yet. You should! Then there's the Microsoft Enterprise Networking Team blog. And if you're looking for help with the Windows Server Core, be sure to check out the Ask the Core Team blog. Excellent stuff! As the Tool Time columnist at SQL Server Magazine, I'm always on the lookout for great free tools that get on-going support from their creators. One common experience is finding a nice debugging tool, only to discover that there's no information on how to interpret the debugger result sets! (SQLIOSim anyone?) That's why I love the Microsoft Advanced Windows Debugging and Troubleshooting blog. Another must-have on your Favorites list.

Enjoy!

-Kev

Follow me on Twitter

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

Old Performance Tuning Recommendations Die Hard

KKline — Tue, 23 Jun 2009 14:19:00 GMT

It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own. In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.

Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005. The document states:

Multiple tempDB Files for Better Scalability of XML Variables and Parameters

XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.

Example: Creating Multiple tempdb Files

This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.

Copy Code

USE TEMPDB

GO

ALTER DATABASE tempdb ADD FILE

   (NAME = 'Tempdb_Data1',

      FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),

   (NAME = 'Tempdb_Data2',

      FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB)

GO

ALTER DATABASE tempdb ADD log FILE

   (NAME = 'Tempdb_Log1',

      FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),

   (NAME = 'Tempdb_Log2',

      FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)

GO

These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.

There was a time when this recommendation made sense. The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios. Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file.

This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket. (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)

However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style. On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.

As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications. I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.

Cheers,

-Kev

Twitter @ kekline

P.S. Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.

P.P.S. I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0. ;^)

Architecture Questions - What's Your Opinion?

KKline — Tue, 23 Sep 2008 17:40:00 GMT

I had a great time speaking with the large communities of SQL Server users as I traveled about Europe last week and much of this week. I'm always impressed by the skill, intelligence, and creativity of these professionals.

And sometimes they raise questions that I think are worth sharing with everyone because they too might've heard the question before or asked it themselves. Here are a few examples. What are your thoughts on these questions?

1. To what extent does compatibility mode effect the behavior of SQL Server? For example, direct catalog updates aren't supported in SQL Server 2005. If we're running in SQL Server 2000 compatibility-mode, can we still access the SYS schema?

2. There are strong architectural reasons why SQL Server doesn't support multiple log readers, right? Let's discuss. (I have my own ideas about why you wouldn't want to enable it.) However, the potential for improving the performance of replication by the use of multiple Log Reader agents is very strong because you could have more data flowing from one server to another. Imagine for example, if you could have one log reader pumping data for the "customer" table and another pumping data for the "orders" table. How can we build a solution that offers us those benefits?

3. Error messages? Yes, error messages. Is there any way we can find out what error messages are most commonly encountered across the SQL Server universe? Maybe someone on the PSS team can help us out here?

4. How do I give a good presentation? Well, there's a hilarious YouTube video that covers all the bases - http://www.youtube.com/watch?v=cagxPlVqrtM&feature=related. But if you're looking for some real resources, here are some others that are useful:

Good web sites from Garr Reynolds and his associated book:
http://www.presentationzen.com/
http://www.garrreynolds.com/Presentation/index.html
http://www.amazon.com/gp/product/0321525655/103-6148611-3957463?ie=UTF8&tag=garrreynoldsc-20&linkCode=xm2&camp=1789&creativeASIN=0321525655

Guy Kawasaki web site:
http://blog.guykawasaki.com/
http://blog.guykawasaki.com/2008/09/winners-of-worl.html
http://blog.guykawasaki.com/2005/12/the_102030_rule.html

How to Give a Steve Jobs Caliber Keynote Presentation:
http://www.maclife.com/article/how_to_give_a_steve_jobs_caliber_keynote_presentation?page=0%2C0

So what do you think about these questions?

Best regards,

-Kevin

WHY ISN'T AUTO_UPDATE STATISTICS RUNNING?

KKline — Mon, 19 May 2008 15:58:00 GMT

I find that auto_update statistics in SQL Server is a really good thing. Without it, many third-party applications would simply fall over from lack of preventative maintenance. With it, they are able to run for extraordinarily long periods of time without really needing a full-time DBA to check up on the databases.

Having said all of that, auto_update statistics is a mystery for many of us. This is reflected in the fact that I get lots of questions about auto_update statistics when I speak publicly (which seems to be at least once per month these days). The most common question is “why isn’t auto_update statistics running?”

There are many elements to the answer to this question. Naturally, you’re not going to have any new statistics in a database that is marked as readonly. However, you might not know that approximately 20% of a table needs to change before triggering an auto_update statistics run for the given table. If the table is really big, it’s possible that you’re simply not changing enough data.

Also remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005. Otherwise, the old statistics are unusable by the query engine. Once you’ve run sp_updatestats, the engine will maintain them automatically. Be sure to check the article “After Upgrading the Database Engine” at http://msdn2.microsoft.com/en-s/library/ms143695.aspx to make sure you covered all the bases.

Thanks to Connor Cunningham and Simon Sabin (http://sqlblogcasts.com/blogs/simons) for help on this post.

NASTY RUMORS ABOUT MAXDOP

KKline — Tue, 15 Apr 2008 17:48:00 GMT

You'd think an article called "NASTY RUMORS ABOUT MAXDOP" would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it'd be total fiction (at least, it would be coming from my pen).

So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading. You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number. I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself. But I’m curious if any readers out there have definitive information one way or the other.

Evidently, there are two roles involved in parallel processing, a writer and a consumer. Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.

Hope this helps,

-Kevin

Reading a SQL Server mini-dump

KKline — Tue, 27 Nov 2007 00:08:00 GMT

I was recently trying to diagnose some problems in one of my SQL Servers when I ran across a problem that I hadn't encountered before involving 'nonyielding worker threads'. SQL Server also produced a dump for me to review. Unfortunately, the dump didn't make a lot of sense to me.

If you encounter one of SQL Server's mini-dump files and want more information, you'll be happy to know that there's a lot of information on-line:

http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx

This URL is also a wealth of diagnostic and internals information that I recommend for any serious SQL Server professional.

Enjoy!

-Kev

P.S. And check out the new CD by Paramore - best alt rock CD of the year IMO.

More Pages Reported Than Actually Exist in the Table

KKline — Fri, 26 Oct 2007 13:38:00 GMT

MVP Hugo Kornelius once reported that he encountered a situation in which it was possible to perform a table scan on more pages than actually existed in the heap table. Hugo deduced that this was due to a phenomenon called “forwarding pointers”.

Why in the world would this ever happen? Real Paul Randall’s excellent blog at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspx for a hint:

“Another drawback [of heaps] is that when scanning through the heap, forwarding records have to followed immediately (as opposed to ignoring them and just reading the forwarded records when they're encountered). This is to vastly reduce the possiblity of read anomalies (such as non-repeatable reads or missed rows if a row moves before the scan point during a scan).”

Evidently, SQL Server chooses some times when it’s appropriate to ignore forwarding pointers for a speed advantage when scanning the entire table.

Interesting details, though minute, to be sure. Enjoy,

-Kev