Friday, April 08, 2011

Statistics in SQL and Finance

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)))) correlation FROM BulbSales x JOIN BulbSales y ON x.month=y.month WHERE 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.

Thursday, April 07, 2011

Alerting for PerformancePoint and Excel

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.

Our Products | Bidoma.com

Sunday, April 03, 2011

Dashboard Design - the definitive guide

Paul Turley has a list of books and articles to read around dashboard design.

I will begin by reviewing these books and discuss the pros, cons and the applicability to the subject of dashboard and KPI design using the Microsoft product stack.

https://sqlserverbiblog.wordpress.com/2010/12/16/dashboard-and-kpi-designthe-definitive-industry-standard-2/

Tuesday, March 29, 2011

PerformancePoint Planning limitations

For those of you (like me) with customers still using PerformancePoint 2007 Planning, there are some limitations to be aware of.

http://social.technet.microsoft.com/forums/en-us/ppsplanning/thread/12A9AD9D-BF5F-4A28-98CD-7827AC75C061

It has nothing to do with how many dimensions there are in a model.



It has everything to do with how many dimensions are in a model site, how many models are in a model site, and how many member sets are in a dimension. It seems that if you add all dimensions, all hierarchies, and all models in a model site they cannot exceed 128 or else you get this error.

The staging database contains a table which can assist to ensure that these limits aren't exceeded.

select * from fk_relationships where scope =

Saturday, March 19, 2011

SQL Execution Plan Tuning

Two ways to find what's going wrong with your queries.

Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?


http://weblogs.sqlteam.com/mladenp/archive/2010/01/21/SQL-Server-ndash-Find-the-most-expensive-operations-in-Execution.aspx


SQL Sentry
http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

My hammer approach is to just save the query plan and then reopen in full screen.

Wednesday, March 16, 2011

Saving time and money with BI Business Appliances

A black box appliance might be better in some cases than one where you can shoot yourself in the foot if you configure the drives incorrectly.

All I can say is the last couple of projects I have been on related to configuring and installing SQL could have been solved by just clicking the Buy button on this.  However, if you need 128GB of RAM instead of the default 96, or a special PCI card for your SAN you're out of luck.  It's not expandable.

The pros and cons and performance of the MS HP Business Decision Appliance from one reviewer.
http://www.sqlmag.com/article/business-intelligence/BI-Appliances-vs-Standard-Servers-and-Reference-Configurations.aspx

Monday, March 14, 2011

SQL Azure - Linked server from local Sql server

Here's one way to simplify accessing Azure.  Create a linked ODBC Server.

Not sure about performance on this one, and be aware there's a chance it could pull all your data down from the cloud, emptying your wallet in the process.

http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

Wednesday, March 09, 2011

Fire more free SQL downloads

courtesy of Brent Ozark.  Cleartrace and SSMS Tools Pack are my daily use tools.

So you’re lazy and you’re company’s broke – what to do?  Here’s my favorite free downloads to help manage SQL Server:

Read more: http://www.brentozar.com/archive/2011/02/my-favorite-free-sql-server-downloads/#ixzz1FNZw8jt5

Sunday, March 06, 2011

5 (now) Free SQL Tools

Atlantis Interactive provide next-generation free SQL tools specifically tailored for performance and ease of use when architecting or administering large Microsoft SQL Server database systems.


Includes an SSMS app with additional features, SQL compare and data compare, table space analyzer, data visualization and analysis, and schema/metadata dependency and analysis tools.


http://www.atlantis-interactive.co.uk/blog/default.aspx

Definitely worth the download(s)

Thursday, March 03, 2011

Resmon - Resource Monitor for Analysis Services

Teo Lachev posts about a new resource monitor tool for Analysis Services.

Greg Galloway just published a ResMon cube sample on CodePlex that aggregates execution statistics (rolls up information about Analysis Services such as memory usage by object, perfmon counters, aggregation hits/misses, and current session stats) from Analysis Services dynamic management views (DMVs) and makes it easily available for slicing and dicing in a cube. I think this will be a very useful tool to analyze the runtime performance of an Analysis Services server or as a learning tool to understand how to work with DMVs. Kudos to Greg!


http://prologika.com/CS/blogs/blog/archive/2010/11/04/resmon-cube-sample.aspx

Tuesday, March 01, 2011

Feature pack for SQL 2008 R2

The Microsoft® SQL Server® 2008 R2 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2008 R2.


As with other releases, SQL 2008 R2 has a feature pack which is required for standalone installs of certain components, and adds additional functionality to the product.

Saturday, February 26, 2011

Sql-Rd addon for Reporting Services

For those looking for Report Bursting and other complex scheduling, exporting, and printing requirements, this tool fills in the blanks with Reporting Services.

SQL-RD is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® SQL Server Reporting Services (SSRS) Reports.



SQL-RD contains powerful system, event and business process automation functions which will make an instant impact on the efficiency of your business.

A couple of the cool features I saw were bulk printing to multiple printers, SMS text messaging, Zipped/PGP'd reports, deferred delivery, even an export to DBase option...

Thursday, February 24, 2011

Two new tools for diagnosing performance bottlenecks with SQL

TraceTune is designed to read SQL Server traces and tell you which SQL statements or or stored procedures are consuming the most resources. TraceTune is the online version of ClearTrace and is maintained by scaleSQL Consulting.


http://www.tracetune.com/





Project Lucy analyzes performance data that you upload. It gives you deep analysis and leverages the collective intelligence of other Project Lucy users.


Nothing wrong with just using a few DMVs either.

Monday, February 21, 2011

On DESKI, Proclarity, and Executive Crystal (Ball) Decisions

Sometimes I wonder whether BI vendors actually use their own tools to look at their market.  If they do, then perhaps they are missing the "sentiment" KPI.

Microsoft lost a bunch of sentiment points by discontinuing BI tools like Proclarity and PerformancePoint planning.  Proclarity as a desktop and dashboard tool met quite a few needs for my clients that aren't filled anywhere else without jumping up price point notches and complexity.  I'm still supporting PerformancePoint, the discontinued flavour.

Now it appears that SAP has taken a similar approach, dropping their desktop client DESKI in favour of their web client WEBI.  Which, of course, has less functionality.  Web apps just don't feel as concrete, physical, or hardened as desktop apps.  Although the technologies are coming together more and more, desktop apps still have points over ease of use and functionality.

How come the BI vendors don't ask their clients how they can serve them better rather than analyzing the P and L for quick-hit cost savings?

IBM (Cognos (formerly SAP (formerly BusinessObjects (formerly Crystal))) developers are an angry bunch!

Am I missing a bracket or two there?

Friday, February 18, 2011

BI in the Cloud or Head in the clouds?

Last week I went to a .NET user group presentation for Microsoft's Azure cloud computing platform.  The topic, though a bit dry, was interesting.  Cloud storage confuses me a bit since I'm a relational database kind of guy.  Azure Cloud Storage provides a basic API to store BLOBs (binary objects like images, music or video) in its massive redundant storage data centers.  It has 3 major types of semi-structured or unstructured storage:
  • Blobs
  • Queues
  • Tables
The coding examples actually offended my sense of good form with databases and coding, probably since the opposite design philosophies are true for Azure Storage apps versus relational database apps.  Or at least this is what I got from the presentation, which might be a bit off.
  • Keys are no longer supposed to be small, integer, incrementing for performance.  
  • Keys are long strings, sometimes with pipe delimiters.  
  • There is no concept of ATOMIC transactions - the app does the work.  
  • A table can hold two different types of object information (invoice/invoice details) with the key differentiating.   Duplicate data is replicated everywhere.  
  • Message Queues have a chance of dropping data which the app needs to handle.  
  • There are limits to the scalability of requests and storage size that the app needs to be aware of and design around.  
  • There's only 1 key on a table, so searching against a non-partition-key field causes table scans.
  • It's better to update / merge data than to append to the end.
The cloud will scale to terabytes, petabytes, exabytes of data depending on your budget, however good luck doing any complex searches against your metadata.  Seems rather painful right now.

Many of the stable, reliable, tried-and-true server and developer tools available outside the "cloud" provide a huge step up in terms of ease of development effort and data quality.  Why are we taking steps backwards in development to accommodate buzzwords and marketing stories?  Is there really a great benefit to moving your enterprise into the cloud?

Cloud in simple words is 'Pay as you use'. Cloud computing is analogous to the way we pay for our utilities like electricity or water based on the consumption. The underlying Infrastructure is owned  and managed by some third party and cost is billed to us in form of units consumed - BI In the Cloud


Driving around eastern Ontario a couple weekends ago, I was amazed by all the farms and houses having solar panels that weren't there last year.  I told my wife some salesperson was doing a good job.  This type of micropower distribution structure seems like the way of the future; not a huge smoke-belching power plant.  Why isn't it the same for the cloud?


The thing that bothered me the most though?  MS has Azure Cloud Data Centers around the world.  But no Azure Cloud Data Centres, eh?  I guess Canada is considered Northern US to the Redmond folks...  :)

Tuesday, February 15, 2011

SAP upgrades its BI tools to 4.0 release

"This release is huge," said Dave Weisbeck, senior vice president of BI and EIM solution management. "Every single tool has had some major upgrades. There's not a single place we haven't touched."

One of my clients I worked with in the past had 2 different versions of their BusinessObjects Universes, with 2 different versions of client and server toolsets running. This was due to the poor migration story BO had, with reports and client tools losing functionality and formatting, a parallel-server migration, and an angry user base (and double the administration) as a result. Hopefully this doesn't happen with the new SAP 4.0 BI tools and there is a clear upgrade path and roadmap.

SAP purchased Sybase last year. Sybase created the original Microsoft SQL product, and MS branched off with their own codebase in SQL 7, further in SQL 2000.  Interesting how many of the BI vendors with a long history (Cognos, Sybase) are being consolidated into the mega software conglomerates.

There may be more than five computers in the world, however it's slowly looking like there's only going to be 5 enterprise BI companies soon..

  • IBM
  • Oracle
  • Microsoft
  • SAP
  • The little guys

Monday, February 07, 2011

Saturday, February 05, 2011

Windows® API Code Pack for Microsoft® .NET Framework

Windows® API Code Pack for Microsoft® .NET Framework provides a source code library that can be used to access some features of Windows 7 and Windows Vista from managed code. These Windows features are not available to developers today in the .NET Framework.

http://code.msdn.microsoft.com/WindowsAPICodePack

Wednesday, February 02, 2011

Office Converters and HTML Viewers

In support of Microsoft’s ongoing efforts to increase the interoperability of its various technologies, we have partnered with Dialogika to create a translator that converts the Microsoft Office binary file formats (.DOC, .XLS, and .PPT) into the Office Open XML standard format (.DOCX, .XLSX, .PPTX).



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


Friday, January 28, 2011

SQL Server - New Drives Use 4K Sector Size - CSS SQL Server Engineers - Site Home - MSDN Blogs

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 - Use Twitter directly from Outlook.

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 

TwInbox - Use Twitter directly from Outlook.

Thursday, January 20, 2011

Programming Solution

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.

Programming Solution

Monday, January 10, 2011

Exists + Descendants function

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

Thursday, January 06, 2011

VBA Functions in MDX | Programming Solution

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.

VBA Functions in MDX | Programming Solution

How to Use Excel's CUBESET Function | eHow.com

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.

How to Use Excel's CUBESET Function | eHow.com

Wednesday, January 05, 2011

Download details: Infrastructure Planning and Design

 

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.

Download details: Infrastructure Planning and Design

Tuesday, December 28, 2010

Query Performance and multi-statement table valued functions - CSS SQL Server Engineers - Site Home - MSDN Blogs

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.

Query Performance and multi-statement table valued functions - CSS SQL Server Engineers - Site Home - MSDN Blogs

Data Type Mapping with Distributed Queries

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.

Data Type Mapping with Distributed Queries

Wednesday, December 15, 2010

SQL Azure Compatibility

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:

  • XML, HierarchyID, Sparse Columns, Filestream data types. (XML support would be a biggie for some clients)
  • Partitions
  • Full-text indexes (again, some clients leveraging this)
  • SQL-CLR

Thursday, December 09, 2010

SQL Server: Top 10 Secrets of a SQL Server Expert | TechNet Magazine

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

Wednesday, December 08, 2010

Report Server Catalog Best Practices - Technical Notes

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

Report Server Catalog Best Practices - Technical Notes

Sunday, October 31, 2010

Memory and Handle Quotas in the WMI Provider Service - Ask the Performance Team - Site Home - TechNet Blogs


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.

IC108955[1]

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.

Memory and Handle Quotas in the WMI Provider Service - Ask the Performance Team - Site Home - TechNet Blogs

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://blogs.technet.com/b/heyscriptingguy/archive/2010/08/26/query-active-directory-with-powershell-and-run-wmi-commands.aspx

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/

Wednesday, October 27, 2010

Open Source Scientific Library (C/C++, .NET, Excel)

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

Open Source Scientific Library (C/C++, .NET, Excel)

Monday, October 18, 2010

Troubleshooting Excel Services

For some reason Excel Services seems a bit convoluted to set up properly.

Some things I had to enable in Sharepoint 2010:
Create a data source to a cube using Other Data in Excel.
Properties of the connection to get the location of the .odc file
Upload the file to trusted Data Sources library
Connect to the data source connection in Sharepoint library from Excel pivot
Upload pivot workbook to trusted document library.

Select the document. View in Browser. Get an error - please try again. (huh)

Go to Site Settings - Features - and enable Excel Services.

Select the document. View in Browser. Works. Refresh data. Errors due to delegation.

Go to Central Admin - Manage Services on Server (Where else would they be) Claims Windows Token Service - Start.

Logs for troubleshooting Excel Services can be found buried in:
Program Files
Common Files
Microsoft Shared
Web Server Extensions
14
Logs

Help is your friend with Sharepoint 2010.

Still, a bit easier to configure in 2010 than in 2007.

Friday, September 24, 2010

SQLVillage.com – Stop Recompilation

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

 

SQLVillage.com

SQLVillage.com – How to run with VS2005/SSIS 2005 on SQL 2008

 

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

  1. Finish the nonproduction servers upgrade
  2. 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.

SQLVillage.com

dtexecRemote - Home

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.

dtexecRemote - Home

Thursday, September 23, 2010

Implicit Conversion issues and SQL performance

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

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

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://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/16/unexpected-side-effects-problems-from-implicit-conversions.aspx

http://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx

SSIS Community Tasks and Components

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

SSIS Community Tasks and Components

Why you should switch from SQL 2000 to SQL 2005/2008/2008 R2


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.

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

Deprecated Database Engine Features in SQL Server 2008 R2

All-In-One Code Framework

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.

All-In-One Code Framework

Monday, August 23, 2010

DMVs for Query Plan Metadata

Excellent article around troubleshooting slow query performance.

This query also allows you to see historical queries executed.

SELECT TOP 100
        max(last_execution_time) LastExecutionTime,
        SUM(total_logical_reads) AS total_logical_reads ,
        COUNT(*) AS num_queries , --number of individual queries in batch
        --not all usages need be equivalent, in the case of looping
        --or branching code
        MAX(execution_count) AS execution_count ,
        MAX(execText.text) AS queryText
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS execText
GROUP BY deqs.sql_handle
ORDER BY 1 DESC

DMVs for Query Plan Metadata