Pythian Group

If you ever used SQL server Database Engine Tuning Advisor then you know you have few options as a source of analysis workload:

- SQL server Script file *.sql

Includes a query or set of queries targeting one or more databases. Tuning advisor will evaluate the script against target databases(s) and attempt to provide any recommendations.

- Trace file using SQL server profiler *.trc

You can use SQL server Profiler to capture different events happening at the instance and record these events on disk.

You can use the GUI to capture the events and watch them in real time then LATER save that trace to a file on disk or use server-side traces and the trace will automatically be saved to the file mentioned in the trace definition.

Read the rest of this entry . . .

If you do any performance tuning , and everyone do some day, then you’ll come to a point where you want to know if your tables have sufficient indexes to serve the queries fast.

Starting with SQL server 2005 , engine tracks indexes usage , through query optimizer, and can now determine if a query can benefit from adding indexes; this information can be identified using few Dynamic Management Views (DMV) including

sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns

You can determine an ***estimate*** of the number of missing indexes per database by running following query

SELECT DB_NAME(database_id) Database_name
, count(*) No_Missing_indexes
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY count(*) DESC;

One important fact is that missing Indexes DMVs can track a maximum of 500 indexes so if you have many databases with many active tables then there’s a chance not all of the info will be present.

I’ve seen plenty are useful queries about creating statements to create these missing indexes but they just give vague names to indexes ,based on sys.dm_db_missing_index_groups fields.

Since it’s a very good practice to standardize your objects’ names , the following query will give names to indexes in following format Read the rest of this entry . . .

Gentlemen (and no Ladies),

Let’s go hairy!!

The 60s are NOT back but during the month of November , some gents , including myself, will grow a stache to raise the awareness of prostate cancer, an insidious disease that afflicts one in seven males.

Movember

Movember (a slang word “Mo” for moustache and “November”) is a worldwide movement in which men, known as Mo Bros, start November clean-shaven and then grow a mustache to bring awareness to prostate and testicular cancer.

The goal is to get family , friends and others to donate money to your Movember cause, which is then donated to the Prostate Cancer Foundation, LiveStrong and other men’s health research and awareness programs.

According to the American Cancer Society, one in every six men will get prostate cancer during his lifetime, and one in every 36 will die from the disease. Behind lung cancer, prostate cancer is the second leading cause of cancer death in men.

There are around 14 countries participating and you can participate through your region or country website.

It’s rather simple: register online as either an individual or a team, start inviting others to participate and maybe create a team then ask others “SHOW ME THE MONEY” . There are prizes for those who raise the most funds.

Grooming is the key

Initially ,the registered participant must start November 1 clean-shaven. Second, maintain your mustache: Grooming is key.

You don’t want to look ridiculous for an entire month or anything, you can do Mexican, Dali, Imperial, Fu Manchu, Pancho Villa, Handlebar, Pencil, Chevron, Walrus…etc.

Here’s a summary of some moustache styles and you can pick one, one of them is NO longer wearable though (You can get arrested in some countries for it)!!

You can go further and even get a copy of The Moustache Grower’s Guide for $9.95

MO Sistas are in too

Although,some say not necessarily, the gents are the ones who grow the MOs but the women play a role too. Some become team captains ; they recruit the “Bros” , endorse them and encourage them grow moustaches.

Statistics shows that lots of donors , up to 60%, of donors are women so ladies you are TOTALLY IN.

Where donations go

The money is used to fund great research projects around the world to fight prostate cancer and other male diseases.

In USA ,$7.5 million were raised for Movember. Worldwide, $174 million were raised and, according to Movember, this makes the group the largest non-government funder of prostate cancer research in the world.

A geeky Movember

I was just looking around for something to welcome Movember and then came something in mind that SQL server 2008/2008 R2 made possible (2005 doesn’t).

SQL server folks, could you please run the attached query in any 2008/2008 R2 SSMS and see what you get in the “Spatial result” tab? you may need to zoom in a bit to get something like this

Now, can you tell me who’s that famous Stache ??!!

Link to Code

Happy Movember,

(Mo)hamed

Microsoft introduced Change Data Capture (CDC) technology in SQL Server 2008.  This technology captures DML (insert/update/delete) changes to a table.  After CDC is enabled for a database and a given table, one can use cdc.fn_cdc_get_all_changes_ function to query changes made to the table. cdc.fn_cdc_get_all_changes_ function returns all columns from the table, even when only one column was updated. It also returns __$update_mask column which is a bit mask that shows which columns were updated, but still – the whole row is returned. When calling this function with “all_update_old” option it returns two records for each update: one with “before update” values and one with “after update” values.  The challenge was to produce a “log” table with columns like this:

Read the rest of this entry . . .

SQL Server 2008 is out of the bag and—luckily for my team—at Pythian we are already seeing customer interest in upgrading, even from SQL Server 2000 and 2005. There are many new features and there will definitely be more blog posts coming from the team regarding them, but for now, I was just browsing around the new 2008 Dynamic Management Views (DMVs), and did a quick overview of some that spiked my attention:

select * from sys.dm_db_mirroring_auto_page_repair

It looks like grouping by db or file id would be useful to zoom in on possible disk issues causing the page errors. Beware—only 100 rows are kept for any database, so if you want to keep a full history you should move those records on a scheduled basis.

select * from sys.dm_db_mirroring_past_actions

This one could be useful for monitoring when there is no witness on a mirroring setup; or for creating a mirroring history report.

select * from sys.dm_db_persisted_sku_features

This one should be used as a warning sign before moving databases between editions, specifically between Enterprise and Standard. Paul Randal has a great post on it: SQL Server 2008: Does my database contain Enterprise-only features?.

Read the rest of this entry . . .