SQL Server with Mr. Denny


October 30, 2008  4:53 AM

VMware Windows XP x64 LSI Driver



Posted by: Denny Cherry
SQL

So yesterday I was asked to setup a few x64 workstations on our ESX server. The Vista x64 machines detected the LSI controller no problem, but the Windows XP machines couldn’t find the controller, and the drivers that VMware included with ESX (3.0.2) were x86 only drivers.

Continued »

October 29, 2008  6:50 PM

Join Jason Hall and Myself as we talk about finding out how healthy your SQL Server Enterprise is



Posted by: Denny Cherry
Jason Hall, Quest Software, SoSSE, Webcast

Tomorrow morning at 8am Pacific / 11am Eastern / 4pm London Jason Hall and myself will be talking about how to find out how healthy your SQL Server Enterprise is using both the native tools as well as Quest Software’s Spotlight On SQL Server Enterprise.

Continued »


October 28, 2008  12:57 AM

New SQL Server Wiki Launching Today



Posted by: Denny Cherry
Brent Ozar, Quest Software, SQLServerPedia.com

Today is the unofficial launch of the new SQLServerPedia.com Wiki.  The official launch for the wiki is during PASS, so you have to go to the /wiki folder manually.  The SQLServerPedia.com site is run by Quest Software, and the Wiki contains all the information which used to be contained in the KnowledgeXpert product which they used to sell.  They are know giving it away for free to the entire SQL Server community.

I could go on all day about it, but forunitely I don’t have to because Brent Ozar already has in his post “The answer to SQL Server training problems“.  I highly recommend reading Brent’s post about the site.  It gives some great background and tells all about the site. Continued »


October 27, 2008  12:46 AM

I’ve finally found myself an offline editor that I like



Posted by: Denny Cherry
Blog, BlogDesk

I’ve finally found an offline blog post editor which I like. With all the traveling that I’ve been doing recently its very handy to be able to edit them offline so that I don’t have to pay for internet access at airports. It also lets me blog while on an airplane without having them write them in Word or notepad and uploading them later.

Continued »


October 25, 2008  11:00 AM

Slide Decks and Sample Code for SoCal Code Camp at USC



Posted by: Denny Cherry
Back To Basics, Federated Database, In Person Events, SoCal Code Camp, Storage

Here are the slide decks and sample code from my sessions at this weekends SoCal code camp.

Back To Basics; Getting Back To The Basics of SQL Server
Scaling that database bigger than ever
Storage for the DBA

While the first two are both two part sessions, there is only one download for both halves as they run together.

Denny


October 22, 2008  11:10 AM

Should I ask my boss to send me to PASS?



Posted by: Denny Cherry
PASS

I would say that yes you should. The PASS summit is an excellent learning opportunity that simply shouldn’t be missed if you can’t avoid it.

No matter how much you think you know about SQL Server, there is always more to learn.

I tend to think I know quite a bit about SQL Server and I enjoy going to PASS, because there is so much that I don’t know about SQL Server, this is a great opportunity to learn from some of the best minds in the SQL Server community and Microsoft.

You can still register for the summit on the PASS Community Summit 2008 web page.

I hope to see you there.

Denny


October 20, 2008  11:05 AM

Thank you Orange County SQL Server Users Group



Posted by: Denny Cherry
Clustering, In Person Events, Microsoft Cluster Service, MSCS, Orange County SSUG, Windows 2003

I’d like to thank the Orange County SQL Server Users Group for having me come and speak at their meeting last week.  I had a great time speaking with everyone, and there were a lot of good questions.

If you didn’t have a chance to download the slide deck you can download it here.  If any new questions come up, feel free to post them here, or email them to me directly.

Denny


October 16, 2008  11:00 AM

Handy Query to look at amount of data in Buffer Cache



Posted by: Denny Cherry
Cache, SQL Server 2005, SQL Server 2008, sys.allocation_units, sys.dm_os_buffer_descriptors, sys.partitions, sys.tables

Here’s a handy query which I’ve written which I’ve used to see how much of each table is loaded into the SQL Server Buffer Cache.  It shows you how much data is in memory, how much space is used on disk, and the percentage of data in memory compared to the amount of data on disk.

SELECT sys.tables.name TableName,
 sum(a.page_id)*8 AS MemorySpaceKB,
 SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
 CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2)) END AS 'Percentage Of Object In Memory'
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id = sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
  AND sys.allocation_units.container_id = sys.partitions.hobt_id)
 OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id = sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
 AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

When you run this code, some of the smaller objects will show more pages in memory than on disk.  It appears that this is normal.  My guess is that SQL Server is preallocating data pages so that if data is added to these pages there is space allocated to them already in order to save time when adding data to the table.

 I’ve removed the system objects from this query, as you can’t do anything to optimize them.

Denny


October 15, 2008  7:07 AM

Attending PASS 2008? Want a free MCP exam?



Posted by: Denny Cherry
Certifications, PASS

Microsoft and PASS have announced that you can take a free Microsoft Certification exam.  Visit the PASS Community Summit 2008 Microsoft Learning Certification Center Registration Site for more information.

I have just registered for one, so thier appear to still be free registrations available.

This program is only available for people who are going to PASS, and you have to take the exam at the PASS summit (which requires attentance to the  summit).

See you at PASS.

Denny


October 13, 2008  11:01 AM

Why is my SELECT COUNT(*) running so slow?



Posted by: Denny Cherry
Query tuning, SQL

Take a look at the execution plan for your query. You’ll notice that the query is doing an Index Scan (or a table scan), not an Index Seek which is why the SELECT COUNT(*) takes so long. The reason for this is that the COUNT(*) function needs to look at every record in the table.

As a workaround you can use the technique that Microsoft uses to quickly display the number of rights when you right click on the table and select properties.

select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id('YourTable')
and spart.index_id < 2

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.
select max(ROWS)
from sysindexes
where id = object_id('YourTable')

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

Denny

(I was informed that my prior information on this post was not quite accurate, so I have revised the post accordingly.  In a nutshell when doing a SELECT count(*) FROM Table even if the row contains all NULLs the record is still counted.)


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: