Database.sqlpermissions

March 19, 2009 at 2:29 pm (Visual Studio) (, , , , )

Raise your hand if you think this is a real pain in the bottom method for editing user permissions? Yeah, me too. Visual Studio Team System Database Edition is far to fine a tool to make us edit XML to set database user permissions. A co-worker has posted a change request on MS Connect. Connect works really well as long as people vote for what you report. I’ve seen several things change in SQL Server or get fixed primarily because of the reports in Connect. So if doing this:

<PermissionStatement Action=”GRANT”>
     <Permission>EXECUTE</Persmission>
    <Grantee>UserRole</Grantee>
     <Object Name=”dbo” Type=”SCHEMA”/>
</PermissionStatement>

Makes you crazy and you would rather type this:

GRANT EXECUTE ON SCHEMA :: dbo TO UserRole ;

Then click on the link and get the word in front out to Microsoft.

Permalink Leave a Comment

Execution Plan Estimated Operator Cost

March 19, 2009 at 1:09 pm (SQL Server 2008, SQLServerPedia Syndication) (, , , , , , )

I’ve said it over and over again, the costs on operators in execution plans, even in actual execution plans are estimates.  You need to understand that when looking at your execution plans. It’s vital because you need to be able to distinguish between the truly costly parts of a plan and the less costly parts of a plan. Don’t believe me? Take a look at this picture and see if you can spot the discrepancy:

cost

Spot it yet?

Add up the costs for the operators visible in the part of the plan…

Yep 125%.  And there’s more to the plan that I’m not showing. I think this one must total near 200%. The statistics are up to date and there’s no consistency errors in the database. These estimates are just off sometimes.

This is a rather costly query being run against Microsoft Dynamics CRM. There were two missing queries identified by the optimizer and about four key lookup operations that I fixed with two index changes. This jumped out and I wanted to share. The fact is, right now, the query is working well. That particular table has one scan and some reads, identified from looking at the STATISTICS I/O, and it’s not even close to 100% of the cost of the query, but I would sure be in a panic if I believed the estimated operation cost.

Permalink 14 Comments