SQLUG.BE Resource Page

SQL Server Forensics – Playing CSI with a database: Slides and Scripts

Steve — Tue, 08 Mar 2011 18:39:00 GMT

Slides and scripts of last weeks session SQL Server Forensics – Playing CSI with a database have been put available for download.

Check out Tom Van Zele's blog and download the files

SQLShare - Lots of SQL Server videos

Frederik — Thu, 24 Sep 2009 07:53:00 GMT

SQLShare has a video channel dedicated to SQL Server and the technologies that surround it. You'll find videos about SSIS, Replication, Performance and more.

http://www.sqlshare.com

Troubleshooting Performance Problems in SQL Server 2008

Frederik — Wed, 15 Apr 2009 09:00:00 GMT

It’s not uncommon to experience the occasional slowdown of a database running the Microsoft SQL Server database software. The reasons can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems; if they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible. This white paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as:

SQL Server Profiler
System Monitor (in the Windows Server 2003 operating system) or Performance Monitor (in the Windows Vista operating system and Windows Server 2008), also known as Perfmon
Dynamic management views (sometimes referred to as DMVs)
SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

We have limited the scope of this white paper to the problems commonly seen by Microsoft Customer Service and Support (CSS), because an exhaustive analysis of all possible problems is not feasible.

Because this paper is very long (102 pages), reading it in an online format such as the Library becomes impractical, so we offer it as a downloadable Microsoft Word document.

- Download “Troubleshooting Performance Problems in SQL Server 2008” from the Microsoft Download Center

Whitepaper - FILESTREAM Storage in SQL Server 2008

Frederik — Fri, 10 Oct 2008 16:10:00 GMT

This white paper describes the FILESTREAM feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system. It covers choices for BLOB storage, configuring Windows and SQL Server for using FILESTREAM data, considerations for combining FILESTREAM with other features, and implementation details such as partitioning and performance.

http://msdn.microsoft.com/en-us/library/cc949109.aspx

SSIS - Microsoft Connectors for Oracle and Teradata by Attunity

Frederik — Sun, 05 Oct 2008 07:27:00 GMT

Download the SSIS Connectors for Oracle and Teradata that provide additional value for SQL Server Integration Services (SSIS) 2008.

Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations

Frederik — Sun, 05 Oct 2008 06:33:00 GMT

This white paper describes a series of test configurations we ran, which represented a variety of possible scenarios involving SQL Server running in Hyper-V. The paper discusses our results and observations, and it also presents our recommendations. Our test results showed that SQL Server 2008 on Hyper-V provides stable performance and scalability. We believe Windows Server 2008 Hyper-V is a solid platform for SQL Server 2008 for the appropriate workload. It is practical to run production workloads under a Hyper-V environment, as long as the workload is within the capacity of your Hyper-V guest virtual machine.

For more information, please refer to the whitepaper Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations.

Learn Microsoft BI - Free videos

Frederik — Wed, 01 Oct 2008 17:27:00 GMT

The goal here is simple: to provide free videos and forums about the Microsoft BI stack. This includes SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, PerformancePoint Server, ProClarity, SharePoint, and Excel.

While the videos and forum participation are free, you'll need to create an account. Simply click on the Sign-up link at the top to set up your account and begin learning about the Microsoft Business Intelligence products.

http://www.learnmicrosoftbi.com/Home/tabid/36/Default.aspx

Offer from Microsoft Learning for the PASS user group community

Peter — Sun, 21 Sep 2008 15:07:00 GMT

Great Microsoft Learning Offers

Microsoft Learning has released a collection of valuable offers for the PASS user group community. Get the Microsoft Learning products that you want for less. Take advantage today of these special offers for home and office users, IT professionals, and developers by visiting: www.microsoft.com/learning/offers

· Get a free “Second Shot” at passing a Microsoft Certification exam; please visit: www.microsoft.com/learning/secondshot

· For savings on SQL Server learning resources, please visit: http://www.microsoft.com/learning/sql/2008

· Get great savings on Virtualization learning resources at: www.microsoft.com/learning/virtualization

Free Microsoft Learning “Snacks”

Learning Snacks are free, short, interactive presentations about popular topics and have been created by Microsoft Learning experts. Each Snack is delivered by using innovative Microsoft Silverlight technology and includes various media, such as animations and recorded demos. Time-strapped? You can learn something new in less than five minutes! Try a “snack” today at:

http://www.microsoft.com/learning/snacks

New whitepapers on SSIS

Frederik — Thu, 04 Sep 2008 07:41:00 GMT

Deployment articles

Reusing Connections with Data Sources and Configurations, by SQL Server MVP Matthew Roche
Understanding Integration Services Package Configurations, by SQL Server MVP Rafael Salas
Best Practices for Integration Services Configurations, by SQL Server MVP Jamie Thomson
Defining a Configuration Approach for Integration Services Packages, by SQL Server MVP John Welch

Performance article

Considerations for High Volume ETL Using SQL Server Integration Services, by Pat Martin, Senior SQL Server Premier Field Engineer for Microsoft New Zealand

Useful DMV queries by Premier Field Engineers

Frederik — Tue, 29 Jul 2008 14:33:00 GMT

The SQL Server Premier Field Engineers in Microsoft UK published a nice set scripts on their blogs.

Check it out here and here.

SQL Server 2005 vs 2000: VIEWS with TOP PERCENT & ORDER BY

Frederik — Wed, 09 Jul 2008 17:22:00 GMT

Author: Gilles Duchêne

The result of a view cannot be sorted. For a lot of developers, this feels like a shortcoming in the SQL language.

Instead of applying the correct syntax:
Creating & using the view:
        CREATE VIEW view AS SELECT fields FROM table

        SELECT fields FROM view ORDER BY fields

A lot of people like to write:
CREATE VIEW view AS SELECT TOP 100 PERCENT fields FROM table ORDER BY fields

SELECT fields FROM view

This looks like a fine workaround, because the TOP PERCENT clause allows you to use the ORDER BY clause in a view (or subquery). However, views or subqueries should only select the rows you need and should never sort them. Sorting of rows must be done as a last step of a query.

So, why can you use ORDER BY in a subquery or view when you use the TOP PERCENT clause too? Because TOP PERCENT + ORDER BY is not intended to sort rows, but to define which rows you want to select, in other words, it is a criterium and not a sort clause. If you want only the first half of the rows of a table (this is a criterium), you can specify TOP 50 PERCENT, but you need of course to specify the field where your TOP 50 PERCENT is based on (is it date, amount, id, name, …, this is the ORDER BY). And this is the big misunderstanding: TOP PERCENT with ORDER BY is a criterium and not a SORT clause.

OK, so? What's the problem, it works, and TOP 100 PERCENT is a good workaround.

Wrong, it worked with SQL Server 2000, but not with SQL Server 2005.
Why? Because in a subquery "SELECT TOP 100 PERCENT", the query optimizer 'recognizes' that this means 'the whole table', so it will not execute the TOP 100 PERCENT with the ORDER BY clause, but it will instead do a table scan (remember that this subquery is only for limiting the number of rows! So, TOP 100 PERCENT = whole table).

Try this example on SQL Server 2000 and 2005 and you will notice the difference:

CREATE TABLE dbo.tblNR
(
nr int
)

Add some records:
INSERT INTO dbo.tblNR (nr) VALUES (1)
INSERT INTO dbo.tblNR (nr) VALUES (2)
INSERT INTO dbo.tblNR (nr) VALUES (3)

Create a view:
CREATE VIEW dbo.vwNR AS
SELECT TOP 100 PERCENT nr FROM dbo.tblNR ORDER BY nr DESC

Use the view:
SELECT * FROM dbo.vwNR

With SQL Server 2000, the output is
3
2
1

With SQL Server 2005 the output is
1
2
3

If you compare both execution plans:
In SQL Server 2000

In SQL Server 2005

That SQL Server 2005 is more efficient becomes clear if you execute following statement:
SELECT * FROM dbo.vwNR ORDER BY nr

In this case SQL Server 2000 will execute a double sort operation (first descending, than ascending):

Conclusion: do not abuse the TOP PERCENT clause to sort rows.

Best Practices for Data Warehousing with SQL Server 2008

Frederik — Tue, 08 Jul 2008 15:47:00 GMT

Summary: There is considerable evidence that successful data warehousing projects often produce a very high return on investment. Over the years a great deal of information has been collected about the factors that lead to a successful implementation versus an unsuccessful one. These are encapsulated here into a set of best practices, which are presented with particular reference to the features in SQL Server 2008. The application of best practices to a data warehouse project is one of the best investments you can make toward the establishment of a successful Business Intelligence infrastructure.

http://msdn.microsoft.com/en-us/library/cc719165(SQL.100).aspx

An Introduction to New T-SQL Programmability Features in SQL Server 2008

Frederik — Mon, 07 Jul 2008 20:21:00 GMT

A great whitepaper on the new stuff in T-SQL written by Itzik Ben-Gan.

http://msdn.microsoft.com/en-gb/library/cc721270(SQL.100).aspx

Data Mining and Business Intelligence for Enterprises by Rafal Lukawiecki

Frederik — Wed, 02 Jul 2008 14:24:00 GMT

In this one-day seminar, Rafal Lukawiecki aims to show IT Professionals how data mining can be used in IT infrastructure to support real business scenarios demystifying the perception that Data Mining is complex, untested or only for specialists. This has become possible since Microsoft has taken the technology to new levels making it accessible to all. In four easy-to-understand yet packed with practical information sessions you will learn about what Data Mining and Business Intelligence can do for you, how to deploy and manage it, how to use it, and how to make it available to other parts of your IT environment.

http://www.microsoft.com/emea/spotlight/event.aspx?id=99

SQL Server 2008 Virutal Labs

Frederik — Sat, 28 Jun 2008 12:49:00 GMT

Test drive Microsoft SQL Server 2008 in a virtual labs.

Keep up-to-date with new lab releases by subscribing to the SQL Server 2008 Virtual Labs feed