SQL Server AWE

Ran across this problem running SQL Server 2005 Ent edition on Windows Server 2003 64-bit with 8gb memory. Even turning on AWE, it still would only use 3gb memory.

Turns out problem is that the account that SQL Server was running under does not have permissions to lock pages in memory.

1. Run gpedit.msc. On the left hand pane, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, select User Rights Assignment


2. On the right hand pane, find Lock pages in memory and double click, then add your Sql Server startup account into Local Security Policy Setting tab;


3. In Sql Server Management Studio, run:
   

   sp_configure ?show advanced?, 1
   
   reconfigure
   
   sp_configure ?awe enabled?, 1
   
   reconfigure

   Ignore the error message below, if you have it.
   

   Msg 5845, Level 16, State 1, Line 1
   
   Address Windowing Extensions (AWE) requires
   the ?lock pages in memory? privilege which is
   not currently present in the access token of
   the process.



4. Reboot.

Updated 10/26/2009:
While looking up this post for helping an friend with the same problem, I noticed that this now works for Standard edition as well as Enterprise edition. You need Cumulative Update package 2 for SQL Server 2008 Service Pack 1 or Cumulative Update package 4 for SQL Server 2005 Service Pack 3 to provide support for Locked Pages in Standard edition.
http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx

Microsoft DreamSpark

 

Microsoft DreamSpark

Today, Microsoft announced that college students can download Visual Studio, SQL Server, Expression Blend, even Windows Server FOR FREE!

How would you like a free copy of Microsoft Visual Studio 2008? How about the entire Microsoft Expression Studio? Not enough...... how about Microsoft Windows Server 2003 and more?
For once, something that sounds too good to be true really is this good and really is true. Starting today (or soon in some areas), students worldwide will be able to download our professional development and design tools for free! It's called DreamSpark and it is upon us.

Wow.  Why?  From PressPass:

PressPass: What is the thinking behind Microsoft DreamSpark? And how did you come up with the name?

Wilson: Microsoft DreamSpark is a community-based program to provide students with free access to Microsoft’s industry-leading software development, gaming and design tools. Working with schools, governments, partners and student organizations worldwide, we will be making this available starting today in Belgium, China, Finland, France, Germany, Spain, Sweden, Switzerland, the United Kingdom and the United States. Other countries will come online over the next year, as well as expanding the program to include high school students. The program is open to all students at education institutions worldwide, though those studying science, technology, engineering and math disciplines (STEM-D) are expected to be the first to jump on it. All eligible students need is access to a computer with an internet connection to download the products, as well as free access keys at http://channel8.msdn.com.

We call it DreamSpark because every great technology breakthrough starts life as someone’s dream or idea. We want to make sure that students have the tools to spark their own dreams plus the power to turn them into reality.

PressPass: Why is Microsoft doing this?

Wilson: We believe students can do amazing things with technology if given access to the right tools. This is a way to make sure that they have what they need to test the boundaries of what today’s technology can do and also prepare for a great career at the same time. The added benefit to industry is that we’re addressing one of the toughest challenges confronting employers today: attracting and developing qualified IT professionals. We’re trying to help close this gap by giving students globally the opportunity to get the tools they’ll need after they graduate and jump-start their careers to land that first job.

Making sure there is a strong pipeline of technically skilled students is key to the future of the global economy. The ability to create new software and services will be an essential part of the skill set of the next generation of workers. Technology is one of the chief drivers pushing worldwide economic development and job creation. As well as giving students important exposure to the tools they can expect to use in the workplace, DreamSpark is about putting professional-level tools in the hands of students to amplify the impact of their studies and fire up their imaginations about the power of technology.

Which is Faster: SAN or Directly-Attached Storage?

Or should I place my database files on SAN or directly-attached storage?

Article on SQLTeam.Com on whether SAN or DAS is better for database files. Quick answer: It depends (of course).

Which is Faster: SAN or Directly-Attached Storage?

SQL Server 2008 Not Until Q3 2008

The Data Platform Insider : Microsoft SQL Server 2008 Roadmap Clarification

Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3.

How to troubleshoot Service Broker problems - SQLTeam.com

How to troubleshoot Service Broker problems - SQLTeam.com

Nice article that lists the different areas to check when troubleshooting problems with SQL Server's Service Broker.

Reporting Services Cribsheet

Reporting Services Cribsheet

SQL Server: Temporarily Changing an Unknown Password of the sa Account

Temporarily Changing an Unknown Password of the sa Account
Handy process for temporary changing SA password then changing it back, for those times when SA password is not known (was not documented and DBA who installed it is gone). Also, could be used to change unknown SA password, assuming that you are willing to put up with and fix possible broken apps/processes using the old SA password.

SQL Server Performance Impact of Procedure Calls without Owner Qualification -- SQL Server 2000

Linchi Shea : Performance Impact of Procedure Calls without Owner Qualification -- SQL Server 2000

The first thing to note is that on SQL Server 2005, the performance degradation caused by procedure calls without owner qualification was less than 5% with 100 or more concurrent users, whereas on SQL Server 2000 the performance degradation with exactly the same test workload was between 15% and 26% with 100 or more concurrent users.

Also interesting to note is the performance increase for SQL Server 2005:

With procedure calls owner qualified, SQL Server 2005 showed 15~18% improvement over SQL Server 2000 under heavy load. With procedure calls not owner qualified, SQL Server 2005 improved transaction throughput by a whopping 35~55% under heavy load.

Doug Seven : Windows Server 2008, Visual Studio 2008 and Microsoft SQL Server 2008 Joint Launch Announced

Doug Seven : Windows Server 2008, Visual Studio 2008 and Microsoft SQL Server 2008 Joint Launch Announced

Note that "Visual Studio 2008 will be released before the end of the year".

Free SQL Tools from Idera

Idera: Tools for Data Management - Products


Idera is making a couple of their tools available for free, including:

SQLsafe backup utility, which compresses backups and makes them run faster ().

SQLCheck, which does performance monitoring and can run as secure screensaver.

SQLPermissions, for copying/moving logins and permissions across servers.

SQL 2005 Service Manager

SQL 2005 Service Manager

Includes version for Vista that works with UAC and without administrative privileges.

SQL Server 2005 Whitepapers

Kimberly Tripp has started a page organizing and listing SQL Server whitepapers.
Blog Entry announcing: Whitepapers, whitepapers and more whitepapers - where the heck are they???
Site: SQL Server 2005 Whitepapers

Heirarchical table functions

Sample code for a function to exploding heirachical where parent key is defined in table itself (i.e. one-to-many heirarchy, item has one and only one parent.


--================================================
-- Create Function for exploding heirarchical table
-- where parentid is defined in table itself
-- i.e. one-to-many heirarchy,
-- item has one and only one parent
--================================================

CREATE FUNCTION dbo.ExplodeList(@id int)
  RETURNS @List_TABLE TABLE (Lvl int, CatID int, ParentID int, [Description] VARCHAR(50))
AS
BEGIN
  DECLARE @lvl int

  INSERT INTO @List_Table
    SELECT 0, CatID, ParentID, [Description]
      FROM dbo.Table_1
      WHERE CatID = @id
  SET @lvl = 0

  WHILE(@@ROWCOUNT>0)
  BEGIN
    SET @lvl = @lvl + 1
    INSERT INTO @List_Table
      SELECT
        @lvl, t1.CatID, t1.ParentID, t1.[Description]
      FROM
        dbo.Table_1 t1 INNER JOIN dbo.Table_1 t2
        ON t2.catID=t1.parentID
      WHERE
        t2.CatID IN
          ( SELECT CatID FROM @List_Table WHERE lvl=@lvl-1 )
  END
  RETURN
END





SQL Server Forums at SQLTeam.com - Article: Presentation: What I Wish Developers Knew About SQL Server

SQL Server Forums at SQLTeam.com - Article: Presentation: What I Wish Developers Knew About SQL Server
Slides from Bill Graziano's excellent presentation at PASS.