Based on a recommendation list by Kevin Kline of Quest Software. This is his recommended list of, if I was only going to buy one or two books in each category of SQL management, here’s what I would buy. They’re on my Amazon wishlist if someone’s feeling generous…anyone…Bueller?
This article applies to CRM 4.0
We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.
On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.
Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.
select
i.accountidname,
i.title,
ir.actualend,
max(timespent) as BillableTime,
isnull(sum(a.actualdurationminutes), 0) as TotalActivityTimefrom filteredactivitypointer a
join filteredincident i on a.regardingobjectid = i.incidentid
left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1
I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.
You can also then build on this and figure out your top accounts using a script like this -
SELECT
Accountidname as AccountName,
COUNT(accountidname) AS Cases,
SUM(BillableTime) AS TimeBilled,
SUM(TotalActivityTime) as TimeWorked,
SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC
Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.
Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.
Once in a while, the SQL box hosting CRM at the office begins to bog down. Here are a few scripts and procedures that I use to help troubleshoot what can be causing the slowdowns.
First of all – start with the basics. Does the server have adequate disk space. How badly fragmented are the drives?
NOTE: The built-in Windows Defrag won’t touch files larger than 2 GB so you may have to defrag by moving the large files onto another volume, defragging, then bringing them back.
Here’s a general SQL script that will show you the process causing high CPU usage. You can then use the SPID to kill the offending process. Insert the name of your organization database into the script as noted.
USE Master
GODECLARE @DATABASE_ID INT
SET @DATABASE_ID = DB_ID(‘organizationname_MSCRM');SELECT ST.TEXT,
SP.*
FROM DBO.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.DBID = @DATABASE_ID
ORDER BY CPU DESC
GO
Once you know which process is stuck, you can kill it using the following command. Insert the correct SPID you gathered from the script above. NOTE: KILLING A RUNNING PROCESS IS NORMALLY A BAD IDEA. Only use this to kill a process that’s hung.
Kill spid
As workflows complete, their records remain in the AsyncOperationsBase table. Over time, this table can grow to enormous size. To resolve the problem, run the following script against your organization_MSCRM database, replacing with your orgname where noted. This is a one-time running script. If you want it to run on a recurring basis, you could set it up as a SQL job.
NOTE: There is sometimes business value in being able to see completed workflows. Make sure you really want them gone before running the script.
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GOdeclare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)
Select @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end
if (@continue = 1) begin
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where B.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId
delete @DeletedAsyncRowsTable
end
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
The above script was taken from Microsoft KB968520
Here’s a script to list open workflow operations on the sql server. These are Workflows that are stuck in a waiting state. Too many and the server starts bogging down. I start getting worried at 50,000 and then at 100,000 we usually see the server start to croak.
Run this against your organization_mscrm database.
Select DISTINCT(asyncoperation.Name ), COUNT(asyncoperation.Name ) AS NUM
from asyncoperation
where StateCode = 1
and RecurrenceStartTime is null
and DeletionStateCode = 0
Group BY asyncoperation.Name
ORDER BY NUM DESC
An example might be a workflow that no longer exists, but has live versions of itself stuck in a waiting state. To remove these orphaned workflows, use the following steps. You can get the workflow name from the script above. Insert into the marked locations below.
PLEASE NOTE: MS DOES NOT SUPPORT editing data directly in the database. You do this at your own risk, and for the sake of all that’s holy, backup your database first.
delete from workflowwaitsubscriptionbase
where asyncoperationid in
(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)
delete from workflowlogbase
where asyncoperationid in
(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)
delete from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1
KB Article 2489162 on Customersource / Partnersource has a good tip on improving CRM Async performance on multi-core servers. Apparently the Async service doesn’t automatically take advantage of multi-core processors. Login to CS/PS and search for article 2489162 to find the article titled How to improve Microsoft CRM Async Performance on multi-core servers
An annoying error that stops SQL reconfigs dead. Sometimes it’s warrented…so reboot your freaking server already. However, sometimes it’s a case of the computer’s right hand not knowing what it’s left one is doing and something gets stuck in the registry. Clearing that pesky stop error can sometimes be resolved by clearing the entries found on the following key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations
Being a geek, I always save the key out before I delete it, but then I’ve been around long enough to have that healthy fear.
The preconference sessions for CRM are sponsored by CRMUG, the CRM user group.
Tips and resources by ColoradoJules
Road Ahead by BIll Patterson
Yippee!! Our bug of “Synchronization with your Android / Windows Mobile / Blackberry has failed” actually is a bug. This was supposedly addressed in several update rollups, but like the Loc Ness Monster, not actually been seen yet. Scheduled to be addressed in Service Pack 1 for Outlook 2010 later this summer.
Why not upgrade:
Why upgrade
Prepping
Preparing Solutions
Infrastructure
Planning
Upgrade Choices
Planning outlook Upgrade
Planning SRS / Email
CRM 2011 Update Rollup 1 has been released. Early fixes and things that were found just prior to RTM.
CRM releases changing to a 6/12 month release cycle rather than a 3 year major update cycle
At my company, we use LogMeIn to provide remote support to our client base. It’s an incredible tool.
However, when installing LogMeIn, it doesn’t work to install thru an RDP session. What happens is that LMI binds to that session rather than to the console and once you logout, there’s nothing left to connect to and you get that black box error about Terminal Server problems.
Here’s the workaround – We’re going to install a VNC server on our target and the VNC viewer on the workstation we’re on. Then when we make the connection via VNC, we’ll be connecting to the console of the server rather than the RDP session we’re using to get there and will be able to install LogMeIn properly.
Our CRM system seems to have slowed over time. One of the reasons is an increase in the number of workflows that are in a waiting state.
I have a SQL script that I ran against the company_MSCRM database to return a list of all the workflow names that were in the wait state, exported the list to Excel and subtotaled it to get a unique count for the number of each workflow. Sort by count and you get a good list of where you need to start optimizing workflows to hit and stop rather than wait for something to happen.
In our case, the number 1 culprit was a workflow that waits for the regarding attribute to be set to an account rather than a case. As a service organization, we try to push all of our service work thru cases. Instead of doing a check and then existing, the workflow ‘waits’ for the regarding to equal an account. This means that every open activity in CRM has a live workflow waiting for the regarding to equal an account.
Here’s the SQL script I used to return the waiting workflow names.
Select asyncoperation.Name
from asyncoperation
where StateCode = 1
and RecurrenceStartTime is null
and DeletionStateCode = 0
ORDER BY asyncoperation.Name
This code is based on an article by JonSCRM on MSDN.
We use virtual machines in-house to test different software configurations. Some of these servers run SQL and a part of that process is renaming the servers. SQL doesn’t like this because it stores its own name internally. Sometimes stored procedures fail, scripts may not work right and SQL Agent jobs may freak out. However, there’s a process for renaming that works pretty well.
On the SQL Server, you can retrieve the internally stored name by running the query:
SELECT @@SERVERNAME AS 'Server Name'
If the instance of SQL you’re working with is the default instance, you can change the servername using the following script:
sp_dropserver OLD_NAME
GO
sp_addserver NEW_NAME, local
GO
If the instance you’re working with is not the default instance, use the script:
sp_dropserver 'OLD_NAME\instancename'
GO
sp_addserver 'OLD_NAME\instancename', local
GO
Note that you can’t change the instance name using this process.
The information here was pulled from Microsoft Technet at http://technet.microsoft.com/en-us/library/ms143799.aspx
01/22/2011 – SQLPass training at UofL in louisville Kentucky
Live notes from sessions
Use Set Statistics ON to get feedback on the efficiency of your statements. Provides results, execution plan, and message that shows efficiency of the statement.
Thumb rule - whenever possible, do not use functions on select statements, far more 'busy'
BAD:
SELECT ModifiedDate
FROM SALES.Customer c
WHERE YEAR(ModifiedDate) = 2005
GOOD
SELECT ModifiedDate
FROM Sales.Customer c
WHERE ModifiedDate >= '01/01/2005' AND ModifiedDate < '01/01/2006'
Local variables are expensive – compiler has to make assumptions about values when creating the plan and account for possibilities.
Instead, use stored procedures with parameters. Compilation uses the actual values when compiling.
NOTE: stored proc execution plan compiles for the values present when compiled. If you rerun with new values, this requires a new plan and may be more expensive.
Action: Check out Cross Apply action.
Presenter Arie Jones arie.jones@pti.net
www.twitter.com/programmersedge
Check out MongoDB video on YouTube
AJ’s 10 second rule – Data vis needs to be understandable within 10 seconds of viewing the report
SQL 2008 R2 includes indicators as part of the available tool elements.
When setting ranges for kpi’s, start is >=, end is <
KPI’s include all the conditiional formatting capabilities of other report elements
Custom images can be imported and used in reports (company logo)
Show a bar-chart style representation of values
To show multiple styles of representation based on user preference, add multiple elements and use visibility property and show / hide based on a report parameter. Can even store user preference in db and pull back into reports using a 2nd dataset for the report.
Sparklines
Show trending over time
Can use tooltips for datapoints to display the values at specific points.
Switching between sparkline types maintains tooltips, conditional expressions, etc.
Spatial Data
Includes mappings by state, by county, checkbox for bing map linking
Metadata is included in the maps so the geolocation is not needed
Running in Windows7, you have to run dev environment as Administrator
New Features
Pagination improvements
Named Excel Worksheet Tabs
Text rotation up to 270 deg
Cache refresh plans
New Web Svc endpoints
Azure data retrieval
<fill additional feature list>
Presenter Louis Davidson – drsql@hotmail.com
Presenter Kevin C. Cross – Elliott Tool Technologies LTD
BAD:
SELECT *
FROM Products
WHERE CATEGORY = 1 OR CATEGORY = 2
AND PRICE <=40
The price <=40 is tied to the second Category, not as a second where clause
GOOD:
SELECT *
FROM PRODUCTS
WHERE (CATEGORY = 1 OR CATEGORY = 2)
AND PRICE <= 40
BAD: Select cast(column1) / column2
Good: SELECT column1 * 1.00 / column2
If a company was deleted by detaching or deleting the DB from SQL, there will be orphaned records in the DYNAMICS database.
In order to remove the company, use the following script -
/* ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS
database referencing databases that no longer exist on the SQL Server.
Requirements:
Company database you wish to have cleaned out of the tables in the DYNAMICS
database must be removed from the SQL server before running this script.
Ensure that all your databases have been restored or they will be erased
from the DYNAMICS database.
*/
set nocount on
/* Remove all references in the company master (SY01500) for databases that
Do not exist on the SQL Server */
delete DYNAMICS..SY01500 where INTERID not in
(select name from master..sysdatabases)
/* Clear out all tables in DYNAMICS database that have a CMPANYID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @CMPANYID char(150)
declare CMPANYID_Cleanup CURSOR for
select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'
+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'CMPANYID'
and o.name <> 'SY01500' order by o.name
OPEN CMPANYID_Cleanup
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
while (@@FETCH_STATUS <>-1)
begin
exec (@CMPANYID)
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
end
DEALLOCATE CMPANYID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a companyID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @companyID char(150)
declare companyID_Cleanup CURSOR for
select 'delete ' + o.name + ' where companyID not in (0,-32767)'
+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'companyID'
and o.name <> 'SY01500'
set nocount on
OPEN companyID_Cleanup
FETCH NEXT from companyID_Cleanup into @companyID
while (@@FETCH_STATUS <>-1)
begin
exec (@companyID)
FETCH NEXT from companyID_Cleanup into @companyID
end
DEALLOCATE companyID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a db_name field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @db_name char(150)
declare db_name_Cleanup CURSOR for
select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'' and db_name <> ''''
and db_name not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'db_name'
set nocount on
OPEN db_name_Cleanup
FETCH NEXT from db_name_Cleanup into @db_name
while (@@FETCH_STATUS <>-1)
begin
exec (@db_name)
FETCH NEXT from db_name_Cleanup into @db_name
end
DEALLOCATE db_name_Cleanup
GO
set nocount on
/* Clear out all tables in DYNAMICS database that have a dbname field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @dbname char(150)
declare dbname_Cleanup CURSOR for
select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''
and DBNAME not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'DBNAME'
set nocount on
OPEN dbname_Cleanup
FETCH NEXT from dbname_Cleanup into @dbname
while (@@FETCH_STATUS <>-1)
begin
exec (@dbname)
FETCH NEXT from dbname_Cleanup into @dbname
end
DEALLOCATE dbname_Cleanup
GO
set nocount on
/* Remove all stranded references from the other Business Alerts table that
no longer exist in the SY40500 */
delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
GO
