Search results matching tag 'Express Edition'

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

Express Edition revisited, focus on SSMS

TiborKaraszi — Wed, 30 Jan 2013 17:01:00 GMT

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.)

I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:

In my 2011 post, I never tried to connect from Express SSMS to a non-Express database engine.
I want to check if there are any significant differences in SQL Server 2012 Express Edition, compared to SQL Server 2008R2 Express Edition.

It isn't uncommon that people want to have SQL Server Management Studio (SSMS) on their machines; and instead of searching for the install files for the full product, they download the freely available Express Edition and install SSMS from there. This was the main reason for this update post, and the reason I focus on SSMS and the tools in this post.

It turns out that both 2008R2 and 2012 RTM Express editions of SSMS includes a lot, but not quite everyting that the full version of SSMS has. And they don't have Profiler or Database Engine Tuning Advisor. 2012 SP1 Express download does indeed have the Complete tool package.

Basic and Complete
The full SSMS (etc.) is referred to as "Management Tools - Complete". This is only available with the Product you pay for and with 2012 SP1 Express. The only one available with the various free Express downloads (prior to 2012 SP1), is called "Management Tools - Basic". You can explicitly request to install Basic from an install media that includes Complete, but you have to explicitly request that in the setup program. You don't want to do that.

One difference between 2008R2 and 2012 is when you install from a pay-media and select that you want to install Express. For 2008R2, you then only have SSMS Basic available. For 2012, you have Complete. In other words, if you use a 2012 pay-media and select Express to install SSMS, you have the option to have the full-blown SSMS - Complete (including other tools, like Profiler).

The downloads
For SQL Server 2008R2, you have "Express Edition" and "Express Edition with Advanced Services". The former is basically only the database engine, where the later has some Tools (SSMS Basic, primarily). See my earlier blog post for more details about 2008R2.

For 2012, there are bunch of downloads available. Note that if you want Complete tools, you need to download SP1 of the installers. You find SP1 here (and RTM, which you don't want to use, here). SP1 includes Complete tools, and you will see that those downloads are significantly larger compared to RTM. It isn't obvious what each exe files stand for, but scroll down and you will find pretty good explanations. I tried several of these (SSMS only, Express with Tools, Express with Advanced Services). They all have in common that for RTM the tool included is Basic, where for SP1 we have Complete.

So what is the difference between Basic and Complete?

In the table below, my focus was on what isn't in Basic. In general, I don't bother to list functionality which is available in both Basic and Complete. So, if the functionality isn't in the table below, it is likely available in Basic. I might have missed something, of course! And my main focus was on SSMS and the database engine.

Component/Functionality	2008R2	2012 RTM	2012 SP1
Functionality in SSMS
Node for Agent	Y	Y	Y
Graphical Execution Plans	Y	Y	Y
Projects and Solutions	N	Y	Y
Maint Plans, Wizard	Y	Y	Y
Maint Plans, New, designer	N (1)	N (2)	Y
Maint Plans, Modify	N (1)	N (2)	Y
Node for SSIS Catalog	N/A	Y	Y
Tools menu, Profiler	N	N	Y
Tools menu, Tuning Advisor	N	N	Y
Connect Object Explorer to:
Analysis Services	N	N	Y
Reporting Services	N	N	Y
Integration Services	N	N	Y
Tools
Profiler	N	N	Y
Database Engine Tuning Advisor	N	N	Y

(1): The selections are there, but they were dead - nothing happened when you select them.
(2): The selections are there, but I got an error message when selecting any of them.

What does this Express Edition look like, anyhow?

TiborKaraszi — Thu, 10 Feb 2011 17:40:00 GMT

Chances are that you rarely get to see the Express Edition of SQL Server if you mainly work with servers. This blog post is inspired from a forum discussion where some functionality is missing in SQL Server Management Studio (SSMS) and I suspected that the tools were installed as a Basic installation instead of Complete installation. So how do I check that? Difficult if I don’t have such an install to look at. I’m training this week and have a few spare machines to play with so I went ahead and installed a couple of SQL Server 2008 R2 Express Editions.

Installation
When you install SQL Server, you are asked what to install. Depending on your installation media, the install is pre-selected for the edition of your SQL Server (the product key is already entered for you), or one of the free editions is selected. You can select any of the free editions even if you have a pre-pidded installation. The free editions are:

Enterprise Evaluation Edition
Express Edition
Express Edition with Advanced Services

I decided to install the two Express Editions, on two different machines, to see what they look like.

Express Edition
This is pretty much only the database engine. Here is what options the Feature Selection gave for installation:

I selected all three. Setup then suggested the instance name SQLEXPRESS. One option I haven’t seen before was the ability to install support for User Instances. The installation took 6 minutes. Setup created a program group:

Microsoft SQL Server 2008 R2

Configuration Tools

SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Installation Center (64 bit)

Import and Export Data (64 bit)

Below services were installed

SQL Active Directory Helper Service, Disabled
SQL Server (SQLEXPRESS), Automatic
SQL Server Agent (SQLEXPRESS), Disabled
SQL Server Browser, Disabled
SQL Server VSS Writer, Automatic

So, this is a pretty much “bare” SQL Server with close to no tools. Or more to the point, no SSMS.

Express Edition with Advanced Services
This edition includes some bells and whistles, especially SSMS. The feature Selection dialog looks like:

Again, I selected all options. Setup then suggested the instance name SQLEXPRESS. The installation took 20 minutes. Setup created a program group:

Microsoft SQL Server 2008 R2

Configuration Tools

Reporting Services Configuration Manager
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Installation Center (64 bit)

Integration Services

Data Profile Viewer
Execute Package Utility

Import and Export Data (32 bit)
Import and Export Data (64 bit)
SQL Server Business Intelligence Development Studio
SQL Server Management Studio

Below services were installed

SQL Active Directory Helper Service, Disabled
SQL Full-text Filter Daemon Launcher (SQLEXPRESS), Automatic
SQL Server (SQLEXPRESS), Automatic
SQL Server Agent (SQL Express), Disabled
SQL Server Browser, Disabled
SQL Server Reporting Services (SQLEXPRESS), Automatic
SQL Server VSS Writer, Automatic

So you get some goodies with Advanced Services, what probably attract most is SSMS.

What about SQL Server Agent?
We usually say that Express doesn’t come with Agent, but the Agent service is installed. But disabled and you won't be able to start it even if you enable the service. There’s logic behind this. You might want to upgrade your Express to a higher edition. Thanks to all bits already on the disk, the upgrade essentially just adjusts to your product key, no installation of files is necessary. See Upgrading from Express Edition to Standard Edition
for info on how to upgrade Express to a higher edition.

Express Edition of SSMS?
There used to be such a beast. But nowadays, you instead have below options:

Management Tools Basic
Management Tools Complete

If you install Express Edition with Advanced Services, your only option is Management Tools Basic. This corresponds to the old SSMS Express. Your SSMS is limited and lacks a lot of functionality. The interesting aspect is that you can also select Management Tools Basic from a “real” SQL Server installation media (Standard Edition, for instance). I doubt anyone wants to do this, but it can happen by mistake. One way to see if your SSMS is Basic is to look for SQL Server Profiler in the Tools menu (thanks Mladen Prajdić). If it isn’t there, you have the Basic installation of SSMS. Read on for another way to check this.

Update 2011-08-06: I just learned that 1) there are some minor differences between Basic from Express install and Basic from non-express install and 2) Basic does have a node (GUI) for Agent, if connected to a non-express instance. I learned it in this thread and you can find out more in this blog post.

How do I find out what is installed?
There’s a great discovery tool built in the setup program, and this is already on your machine:

Start menu
Microsoft SQL Server 2008 R2
Configuration Tools
SQL Server Installation Center.

Here you select:

Tools
Installed SQL Server features discovery report

This will lead you to a HTML page which clearly states what SQL server stuff is installed on your machine. It will tell you the edition of the server components, and it will tell you whether your tools are installed as Basic or also Complete (if you installed the “full” SSMS, you see both Basic and Complete).

November CTP of SQL Server 2008 R2 : Download now

AaronBertrand — Wed, 11 Nov 2009 22:44:00 GMT

For those of you that have been wanting to try out SQL Server 2008 R2, you can download the November CTP of Express here (posted yesterday) and the full version of the November CTP here. This is version 10.50.1352.12. The full version is an evaluation edition and will expire after 180 days. I am not sure when the full version of the CTP was posted publicly; earlier this week, that privilege was restricted to MSDN / TechNet Plus subscribers (and TAP a.k.a. early adopter customers).

For the Express Edition, if you scroll down the page, you will see that you can also download the Express version of Management Studio (SSMSE) for both 32-bit and 64-bit systems, without having to download the entire engine and other services. This was announced earlier today by @SQLAzure in a blog post. In previous releases of Express, it wasn't always easy to find the Management Studio Express Edition downloads, and they often came later than the combined downloads (w/Tools or w/Advanced Services). Most users ended up downloading one of those and then trying to install just the management tools.

For those of you who are unwilling to download any of these pre-release builds onto your machines, and who don't have virtual machines you can use for this purpose, you can get limited exposure to the new engine and some of its features by signing up at sqlserverbeta.com (you can read more about this program in a recent blog post by Jorge Segarra (@SQLChicken)). Currently their front page advertises a lab for the August CTP (build 10.50.1092.20), but this could just be a lag in marketing materials. So I'm not sure if they are currently offering the latest CTP build of R2, or if you will have to wait for that to become part of their offering.

Download a standalone installer for SQL 2008 Management Studio Express

AaronBertrand — Sat, 21 Feb 2009 15:32:00 GMT

In October, I complained (surprise, surprise) on behalf of Express users that you had to download an entire Express package (with Tools or with Advanced Services) in order to install the basic Management Studio Express, even if you already had the engine installed and had no need for another instance. (For SQL Server 2005, a standalone installer was made available very early on.) While articles like this one by fellow MVP Andrea Montanari proved to help a lot of people figure this out, there still seemed to be a loud cry for a standalone installer that didn't require jumping through all of these hoops.

Well, better late than never. Bill Ramos announced yesterday that they have released a standalone installer for SQL Server 2008 Management Studio Express, in both x86 and x64 flavors. You can download it here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b

Now, when I download the 64-bit installer, it is 175 MB, and it seems to be a full-on setup program for either Express with Tools or Express with Advanced Services (I stopped the install when it asked me if I wanted to create a new instance of SQL Server 2008 or to modify an existing instance). I will download again later this weekend and try it out, but so far, it is not the "no-frills" installer that Bill advertises. If you have a different experience, please let me know.

SQL Server 2005 Express SP3 might already be on your system

AaronBertrand — Wed, 28 Jan 2009 15:30:00 GMT

Early reports indicate that some users running Express <= SP2 are getting SP3 for Express pushed to their system automatically as an important update, rather than recommended, which means that some users are getting it without even knowing about it. Even though the official Release Services blog states that this behavior shouldn't start happening for another two weeks. So just as a warning, if you don't want SP3 just yet (and if it isn't already installed), turn off automatic updates until this gets sorted out. If I hear anything back from Microsoft on this issue, I will post here.

SQL Server Express 2008 is now available

AaronBertrand — Tue, 12 Aug 2008 11:02:00 GMT

Sorry I missed this yesterday with all the excitement over the Visual Studio SP1 release (which allowed some people to finally install the SQL Server 2008 release from last week). Yesterday also marked the release of SQL Server 2008 Express Edition, which you can download here:

http://www.microsoft.com/express/sql/default.aspx

You can read more background about it at the sqlexpress blog; if you have a previous edition of Express on your machine(s), you should read this for important setup/upgrade information:

http://blogs.msdn.com/sqlexpress/archive/2008/08/11/sql-server-2008-express-is-now-live.aspx

Note that only the core edition of SQL Server Express has been made available; you will likely have to wait a few weeks before you see the "with Tools" and "with Advanced Services" releases that I talked about the other day.