Brent goes into detail about how important functional requirements are versus fuzzy requirements.
do you want it to be 10% faster, 100% faster, or 1,000% faster?”
Brent Ozar - Too Much Information
1000% please….
A dump of all things SQL Server, SQL Reporting Services, Analysis Services, around Business Intelligence, Business Performance Management and technology.
Brent goes into detail about how important functional requirements are versus fuzzy requirements.
do you want it to be 10% faster, 100% faster, or 1,000% faster?”
Brent Ozar - Too Much Information
1000% please….
Correlation is important when dealing with risk management or forecasting calculations. The closer to 1 you are, the closer the numbers are correlated. Consider if you purchase GOOG and MSFT stock. Since they are both in the tech sector, they could be part of a combined selloff, which means your risk is increased if you own both in your portfolio.
The Select Sector SPDRs Correlation Tracker identifies GOOG as being 0.46 correlated to MSFT, which appears low.
You can use Correlation for forecasting results and determining trends in SQL. Consider this query:
SELECT(COUNT(*)*SUM(x.Sales*y.Sales)-SUM(x.Sales)*SUM(y.Sales))/(SQRT(COUNT(*)*SUM(SQUARE(x.Sales))-SQUARE(SUM(x.Sales)))*SQRT(COUNT(*)*SUM(SQUARE(y.Sales))-SQUARE(SUM(y.Sales))))correlationFROM BulbSales x JOIN BulbSales y ON x.month=y.monthWHERE x.Year=1997 AND y.Year=1998
Transact-SQL Cookbook: Chapter 8: Statistics in SQL
If the correlation is closer to 1, the numbers are similar. Comparing various years and months of sales or expenses can easily uncover correlations. This could also work for tracking correlation dependencies between expenses and revenue.
SQL has a number of formulas that are useful for identifying patterns and trends in your data.
Bidoma is an email/desktop alerting engine that notifies of changes to Excel worksheets and PerformancePoint dashboards. Could be a good measure for spreadsheet controls, master data management and timely responses to KPI changes.
Enables OpenXML documents to be viewed on any client system in any browser as HTML, and includes an add-ins for Internet Explorer, Firefox and Opera (Windows/Linux)
http://www.interoperabilitybridges.com/projects/openxml-document-viewer
Enables bi-directional interoperability between Microsoft Office (Open XML) and Open Office (ODF) file formats
http://www.interoperabilitybridges.com/projects/openxml-document-viewer
Including one command, SAVE TRANSACTION, that could be useful for multiple iterations of a task with error handling.
Some good pointers for tuning SSAS 2005 cubes here.
Business Intelligence: SSAS 2005: Cube Performance Tuning Lessons
Bob Dorr talks about drive sectors, new standards, and implications on SQL Server. Somehow this seems to me to be a conspiracy to sell SQL bundled with appliance hardware that is already configured for SQL. I can see why there is a move to a SQL operating system and dedicated appliances rather than dealing with all the intricacies of the o/s and 3rd-party hardware, firmware updates, drive arrays and configuration, memory settings, disk alignment, partitioning, raid configurations, etc.
The implications of incorrect drive sector settings? Risk of losing data, slow performance and not being able to restore databases to upgraded hardware.
In a lengthy discussion this past week I was reminded that Jan 2011 is when the hard drive manufactures agreed to focus on drives with sector sizes of 4K. I have read all the latest materials about this over the past week and you can too. Just search for 512e or Advanced Format Sector sizes and you will find the same articles I read. I concentrated on articles by Seagate, Western Digital and other manufactures.
Why am I talking about this on a SQL Server blog? - The change has impact to your SQL Servers. There are two areas you need to be aware of. PERFORMANCE and DATA INTEGRITY
SQL Server - New Drives Use 4K Sector Size - CSS SQL Server Engineers - Site Home - MSDN Blogs
Twinbox + Access Linked Table to Outlook Folder = Quick and dirty Twitter mining.
If you want to archive your twitter posts, search posts offline, or just hack around with the data, Access allows you to link to Outlook folders, including your Twinbox folders.
If you are using Twitter and Outlook and haven’t tried Twinbox, you’re missing out.
http://msdn.microsoft.com/en-us/library/aa164914(v=office.10).aspx
Taking the U out of CRUD for performance reasons.
So, to conclude, we can say that if we have to do many updates in a very large table (table with several million rows), we can avoid update statement and use select and insert.
Returns the set of members based on the member attribute in MDX.
EXISTS(DESCENDANTS([Account].[COA],,leaves), [Account].[AccountType].[Revenue])
IBM Information Management Software for z/OS Solutions Information Center
Left, Right and Contains in MDX.
There are some VBA functions that are implemented in MDX code base and so they are faster than using VBA functions. We can call them “internal VBA” as by Irina Gorbach. Here we will see some example of such “internal VBA” in MDX.
For Cube function reporting in Excel, one function that doesn’t get translated during a pivot table formula convert is CUBESET.
Here is a way to get the leaves (lowest levels) of a dimension using the cube set formula in Excel 2007/2010.
=CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")
To retrieve the members, create a list of numbers ending at the CUBESETCOUNT() of above, and use the following formula:
=CUBERANKEDMEMBER(connectionstring,
CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")
,1))
Retrieves the first member of the set.
The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.
Common Table Expressions (CTEs) and table-valued functions (TVFs) are features in SQL 2005/2008 that solve a lot of problems for me in terms of functionality and compartmentalizing code.
They also cause issues with performance for larger datasets. There are some ways around this. Hopefully SQL.Next solves some of these issues for us…. in the meantime.
Lately I worked with a customer to help tune his query involving multi-statement table valued function. When using table valued functions, you should be aware of a couple of things
First, there are two type of table valued functions which are inline table valued function (Inline TVF) and multi-statement table valued function (multi-statement TVF). Inline table valued function refers to a TVF where the function body just contains one line of select statement. There is not return variable. Multi-statement table valued function refers to a TVF where it has a return table variable. Inside the function body, there will be statements populating this table variable. In the demo at the end of this blog, there are examples of inline TVF and multi-statement TVF.
Secondly, multi-statement TVF in general gives very low cardinality estimate.
For those using linked servers or SSIS/DTS to external databases, this MSDN article provides details on data type mappings.
An OLE DB provider exposes the data types of its data according to OLE DB type identifiers called DBTYPEs. Data types are converted between OLE DB data types and SQL Server system data types by mapping data:
From OLE DB data types to SQL Server system data types. This conversion occurs when SQL Server reads data from the OLE DB data source, either in SELECT statements or in the reading side of UPDATE, INSERT, or DELETE statements.
From SQL Server system data types to OLE DB data types. This conversion occurs when SQL Server writes data, mostly in INSERT or UPDATE statements, into the OLE DB data source in which the modified table is a remote table.
An enormous 95-slide deck has been published as a briefing on cloud computing at MS and the Azure platform.
Slide 67 has some of the features in and out of SQL Azure.
What’s not supported:
Paul Randal provides 10 musts for SQL DBAs
SQL Server: Top 10 Secrets of a SQL Server Expert
Maintaining a SQL Server environment is a potentially complex endeavor. Here are the top 10 ways you can minimize complexity and reduce stress.
SQL Server: Top 10 Secrets of a SQL Server Expert | TechNet Magazine
Excellent best practices guide for RS 2005 installs
Report Server Catalog Best Practices
Building and Deploying Large Scale SQL Server Reporting Services Environments—Technical Note Series
Not necessarily related to BI or SQL unless perhaps you are reporting on performance or software inventory, or using it for an ETL process. However, wmiprivse.exe is one of those important Windows mystery components that does a lot behind the scenes and is good to be aware of if you are a developer, and good to be an expert of if you are a support person. It also seems to come up often as a big consumer of CPU and memory. To me it always seems like a virus since it takes over my Windows randomly and pops up multiple processes, without any details on which application is generating them.
It really is quite a simple piece of software.
Well, not so much.
This article describes how to configure the component’s quotas to ignore some needless errors that some monitoring tools will throw.
When the WMI Provider service reaches its quota limit, WMI queries that are being handled by that instance of WMIPRVSE.EXE will most likely fail. However, there are applications like System Center that may require more memory or handles for the process. These quotas are configurable, however – do not modify these quotas for the sake of modifying them! If the WMIPRVSE.EXE process is actually leaking handles or memory, modifying the quota will only delay the issue from occurring, not eliminate it. In instances such as these, normal leak troubleshooting must be performed to identify the root cause.
More on WMI at Wikipedia.
And further on the (needless?) complexities of WMI.
Greg was quite enthusiastic about the power of Windows PowerShell but during the BOF someone asked him directly, "So which is it - should we be using VBscript or Windows PowerShell to script SMS". I forgot exactly what Greg said but it was along the line so of, "if you want to do reporting, use Windows PowerShell because it is very powerful but if you want to get things done, use VBScript because you can't invoke WMI methods (at least not easily)".
This was a real wake up call for us. Greg is exactly the sort of person that we want to be a ardent, unabashed, no reservations, hard core Windows PowerShell user and the message was loud and clear - almost but not quite.
We knew that our WMI support was not what we wanted it to be but as I've mentioned many times, to ship is to choose. We thought that we had reasonable support and that WMI users wouldn't be phased by some of the deficiencies - yes calling methods was complex but we figured that if you were using WMI, you were self-qualified as being able to cope with complexity :-) .
Kind of sad that an MS team ignored implementing functionality with WMI because they had issues with the complexity of their own product. Perhaps changing the names and methods for Windows Management Instrumentation to something a little bit more obvious would help with that complexity. The WMI counters and naming conventions appear to come from the days of Windows 95.
WMI is a very powerful and sometimes misunderstood provider. Some of the useful functions:
1. Get drive space for all drives & computers on a network.
2. Perform a software/hardware inventory
3. Start and schedule remote applications
4. Reboot computers remotely
5. Read and consolidate event logs
Being able to right-click on a service or device to explore WMI properties seems the most obvious piece of functionality that was left out of Windows.
Perhaps it could be used as a BI tool, to better understand how your network is functioning and mine data on how drive space is used? Get started with mining data using WQL.
Some more recent info on using Powershell and WMI.
http://www.hackyeah.com/2010/08/powershell-pc-info-script-wmi/
http://www.computerperformance.co.uk/powershell/powershell_wmi.htm
Enterprise Event Forwarding
http://blogs.technet.com/b/otto/archive/2008/07/08/quick-and-dirty-enterprise-eventing-for-windows.aspx
WMI for Analysis Services
http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/thread/CA9B89EE-EC6A-4025-AFDC-0AD852C594B6
SQL WMI Provider
http://yongslee.wordpress.com/2010/02/15/sql-wmi-provider/
Comparing WMI to SQL
http://technet.microsoft.com/en-us/library/cc180454.aspx
OLE-DB to WMI
http://msdn.microsoft.com/en-us/library/h63swas7%28VS.80%29.aspx
WMI Explorer
http://www.ks-soft.net/hostmon.eng/wmi/index.htm
WMI Event Watcher for SSIS
http://msdn.microsoft.com/en-us/library/ms141130.aspx
WMI Data Reader Task
http://msdn.microsoft.com/en-us/library/ms141744.aspx
WMI Blog
http://blogs.msdn.com/b/wmi/
Trying to understand complex equations in Excel for reverse-engineering formulas? The Excel Equations Add-in converts Excel formulas to graphical formula equivalents.
Find it here.
http://www.codecogs.com/components/excel_render/excel_install.php
Driving global filters with Excel and Sharepoint.
With stored procedures that are called hundreds or thousands of times per minute, recompilation is a killer. Some tricks to avoid.
SET Option that Causes Stored Procedure to Recompile
By Mohan Kumar -- Published on 09/14/2010
We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".
How to Resolve a BIDS 2005 and SQL Server 2008 Compatibility Issue
By Mohan Kumar -- Published on 05/24/10 (Earlier published on www.sqlservercentral.com on 09/08/13)
We all know that Microsoft has introduced new feature/component in SSIS 2008. They also renamed few system procedures for SSIS.
I was working on a SQL Server 2008 upgrade project that included SSIS, but the requirement was to hold our SSIS upgrade until the Production upgrade was completed. Therefore, it was important to provide the ability to developers to design and develop their SSIS packages using Visual Studio 2005 but store the packages in SSIS 2008 storage in order to
- Finish the nonproduction servers upgrade
- Do not let developers to use SSIS 2008 features until the production server was upgraded so that any package under development can go live (if needed) on the existing SQL Server 2005
I encountered bunch of errors while saving the package that I had opened in Visual Studio for some changes and later trying to save that package in SSIS 2008 storage. Below is the complete scenario that explains what error messages occurred and how to resolve them.
One way to get around remotely executing an SSIS package …. without SQL Agent.
Comments
vdeineko wrote Mar 26 2009 at 3:42 PM
How to remotely execute MS SQL SSIS package
I find my solution the easiest of all. It does not require neither SQL Agent job, nor SMO, or any Visual Studio development , just one-line primitive scripting.
1. Say, you created a local SSIS package, called MySSIS.dtsx in the directory c:\MyDir, located on the SQL Server, named MySQLServer.
2. Now, create a simple batch file (or a vb script) on the server allowing you to execute the 'dtexec' command. Something like this:
dtexec /FILE "C:\MyDir\MySSIS.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
(Full 'dtexec' command options are here: http://technet.microsoft.com/en-us/library/ms162810.aspx)
Save the batch file, say, as 'c:\MyDir\MyBatch.bat'
3. Create a Scheduled Task to execute the batch file. Name it, say, as 'MyTask.' You can set the task to execute once, and even with the past execution time. This way it really will not execute, unless you manually execute it. Test the manual task execution by running the following command from the command prompt:
>schtasks /run /tn MyTask
(More on the ‘schtasks’ options type schtasks /? at the command prompt.)
Well, if everything is correct, it will work, so let's do the next step.
4. Download the PSEXEC tool from here:
http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
It's free, and it will allow you to remotely execute programs. Install it on your PC, from where you would execute the remote SSIS. Say, you installed it in the c:\pstools directory.
5. Lastly, open the command prompt and type
>"c:\pstools\psexec" \\MySQLServer schtasks /run /tn MyTask
6. Of course, you can encapsulate the last command into a vbs script or a batch or a MS Access Macro, and just click to execute it.
Resuming the above: You need to execute PSEXEC command to execute the remote SCHEDULED task which executes the local SSIS package. The solution seemlessly bypasses the SQL licensing restriction.
Please let me know what you think.
One commonly-missed performance tuning option is to ensure that parameters and variables passed into SQL are the same data type as the columns they are referencing.
If you pass in an @nvarchar(25) variable to a varchar(15) column, an implicit conversion is done. This is performed on the entire resultset, which could impact performance.
It shows up often when using .NET apps and with developers unfamiliar with SQL schema.
That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.
In SQL 2005+, finding implicit conversions is straightforward with DMVs (and a not so straightforward query).
If you don’t want to rewrite your app, consider rewriting stored procedures (you do use stored procedures?) to convert mistyped variable before the query statement.
Why is ADO.Net so slow? - U2U Blog
This is also a good reason to avoid ORM frameworks (or learn how to use explicitly typed parameters) when dealing with complex SQL queries or large data sets.
More info around LINQ and implicit conversions.
http://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx
Useful set of components available for SSIS
Project Description
This is a community project for developing additional tasks and components for SSIS, and identifying other CodePlex projects that do the same. 51 Integration Services extensibility projects exist on CodePlex - find them and over a hundred more from around the 'net right here.
This list includes projects hosted on CodePlex, items that are available in source or binary-only form for free on other sites, or are commercially available items from third party companies. If there are errors or ommissions in this list, please contact me directly, or post in the Discussions
Other than the support lifecycle and improvements with query performance and administration, there is another reason to upgrade your databases to 90 compatibility mode.
Compatibility levels are only available for the last two versions. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).
Note that even if you have performed a SQL 2005 upgrade of your databases, the compatibility mode may still be set at 80 (SQL 2000) mode.
Best to run the upgrade advisor and have a disaster recovery plan in place before switching compatibility modes.
The All-In-One Code Framework from Microsoft provides most anything in any MS language you would need to start building an app.
Not only C++, includes samples for WPF/WCF, ASP.NET, Silverlight and even Azure.
Some useful links here.
Patterns and Practices Developer Center: http://msdn.microsoft.com/en-us/practices/default.aspx
Microsoft Solution Accelerators: http://technet.microsoft.com/en-us/solutionaccelerators/default.aspx
Application Architecture Guides: http://apparch.codeplex.com/wikipage?title=Knowledge%20Base%20Index&referringTitle=Home&ProjectName=apparch
Application Architecture Guide Free Book: http://apparchguide.codeplex.com/
Microsoft Operations Framework: http://technet.microsoft.com/en-us/library/cc506049.aspx
Patterns and Practices Symposium: http://blogs.msdn.com/b/agile/archive/2010/08/25/the-patterns-amp-practices-symposium-redmond-2010.aspx
Patterns and Practices document from Microsoft, for data: http://msdn.microsoft.com/en-us/library/ms998446.aspx
Article by Brian Knight on Database Standards and Conventions: http://www.sqlservercentral.com/articles/Naming+Standards/sqlstandards/176/
SQL Server Best Practices: http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
Also - The information for making a "default template" for SSMS queries is here in this post: http://blogs.msdn.com/b/buckwoody/archive/2009/10/28/templates-for-new-query-window.aspx
Buck Woody : Links for Patterns and Practices for the Data Professional