Pythian Group

Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement

SELECT * FROM
MASTER..SYSPROCESSES
WHERE BLOCKED != 0;

Read the rest of this entry . . .

1. Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More
2. MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence, Read More
3. Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more
4. Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more
5. Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update with main product including MS Update, WSUS, local folder or UNC. This feature is an extension to Slipstream Functionality which was available in SQL Server 2008 PCU1. We can use command prompt or configuration file to override default values to find updates by specifying values for UpdateSource parameter, Read more
6. We can now install MS SQL Server on Windows Server 2008 R2 Core SP1, not all features are supported though, Read more
7. SQL Server Data Tools (formerly, BIDS) is now part of setup, with this we can carry out all database design work, and can build solutions for SSAS, SSRS and SSIS. Read more
8. Now, Support for SQL Server multi-subnet clustering included, Read more
9. Databases (systems and users) can now be on UNC path, we may need to make sure that proper NTFS and File Share permissions are assigned, Read more
10. Local disk can be used for Tempdb in cluster, Read more
11. Built-in Administrator and Local system account will not be part of sysadmin role
12. Itanium editions are no longer supported

– Hemantgiri S. Goswami

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 . . .

PYTHIAN NEWS UPDATE

For the second year in a row, Pythian is proud to be named to the prestigious Profit 200 Ranking of Canada’s Fastest Growing Companies.

Ranking Canada’s Fastest-Growing Companies by five-year revenue growth, the PROFIT 200 profiles the country’s most successful growth companies. Published in the Summer issue of PROFIT and online at PROFITguide.com, the PROFIT 200 is Canada’s largest annual celebration of entrepreneurial achievement.

Pythian is also happy to announce the official opening of our Toronto, Canada office and the expansion of our local GTA team to include Regional Sales Director, Colin Thompson (previously with Oracle).

Look for the Pythian team at one of the following popular database industry events in Q2, 2011.

We’d like to hear from you. If you have any feedback on our sessions, please send your comments directly to the speaker, or to Vanessa Simmons, Pythian Director of Marketing.

Follow this link to sign up to receive notice of future speaking engagements, webinars or Pythian news.
Read the rest of this entry . . .

A few days ago I learned about this year’s NoCOUG SQL Challenge and decided to to put the gray matter between my ears to work. I’ve been teaching a MySQL course this week and my first impulse was to use my MySQL VM to test my solution attempts. However, I eventually decided to use Recursive Subquery Factoring to solve the proposed problem and had to switch to an Oracle 11gR2, since it’s the only database that implements this feature that I know how to use (are there any others?).

I was happy with my solution, but frustrated that I couldn’t run it on MySQL. So I decided to try to make it somehow work on MySQL.
Read the rest of this entry . . .

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 . . .

I’m here at the PASS Community Summit 2010 in Seattle, WA. This happens to be the largest SQL Server conference in the world with regards to content, attendees and Microsoft participation. With the conference center very close to the Microsoft Redmond campus, Microsoft can afford to send as much as they can from the SQL Server team – from the development team, CSS team, SQLCAT as well as from their business platform group. There have been several announcements made in today’s keynote, few of which were just waiting to be blogged about from the insiders since earlier this year. First of which is the release of SQL Server 2008 R2 Parallel Data Warehouse. Formerly codenamed Project Madison, this was used to be DATAllegro, Inc. – a provider of breakthrough data warehouse applicances – before Microsoft bought them over back in 2008. SQL Server 2008 R2 Parallel Data Warehouse will be readily available in the market so that customers can take advantage of having a data warehouse appliance that runs the very familiar Microsoft SQL Server product.
Read the rest of this entry . . .

Welcome to Log Buffer, the weekly roundup of database industry news.

For your reading pleasure this week we have Log Buffer #196:

Charles Hooper blogs about an in-depth investigation on what can cause Oracle to ignore a hint.

Doug Burns reminds his readers that there are only two weeks left to submit papers for UKOUG. The deadline is Aug. 2.
Read the rest of this entry . . .

We’re well into summer and almost at our 200th edition of Log Buffer, a blog of blogs about the database world.

Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note outlining why you think that particular post would be of value to other DBAs, or what you learned from reading it.

Now on to our weekly reading in Log Buffer #194:
Read the rest of this entry . . .