Here are some great resources for learning more about SQL and finding tips for troubleshooting:
SQLskills.com
MSSQLTips.com
SQLServerCentral.com
SQL Server Performance
Here are some great resources for learning more about SQL and finding tips for troubleshooting:
SQLskills.com
MSSQLTips.com
SQLServerCentral.com
SQL Server Performance
Here are a few links to some great references on optimizing SQL transaction log throughput. In particular, these articles discuss key tips regarding the sizing of transaction logs and the impact too many or too few virtual log files (VLFs) can have on overall performance:
8 Steps to better Transaction Log throughput
Transaction Log VLFs – too many or too few?
Bug: log file growth broken for multiples of 4GB
How to determine SQL Server database transaction log usage
Geek City: Exploring the Transaction Log Structure
Understanding Logging and Recovery in SQL Server
Here’s a link to a Microsoft KB article you never want to be without when moving SQL databases:
Here’s a quick SQL query to show the status of currently active mailbox index movement tasks:
select * from TaskStatus where TaskName = 'IndexMove' AND ProcessedCount = 0 AND FailedCount = 0
Note that the ProcessedCount
will equal 0 for all currently active jobs and previously failed jobs. To list failed mailbox index relocation attempts, set the FailedCount = 1
, e.g.,
select * from TaskStatus where TaskName = 'IndexMove' AND ProcessedCount = 0 AND FailedCount = 1
Here’s a rather complicated SQL query to list the top 10 largest mailbox indices on your NearPoint server:
SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK) WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID AND LV.LogicalVolumeID=IL.LogicalVolumeID ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC
To find the top 10 largest mailbox indices on a particular index volume, include the WHERE
condition AND LV.SharePath LIKE '%INDEX1%'
where INDEX1 is the share name of one of your index volumes:
SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK) WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID AND LV.LogicalVolumeID=IL.LogicalVolumeID AND LV.SharePath LIKE '%INDEX1%' ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC
Note that the mailbox index chunk size in contained the XML data stored in the IndexStastics column in the MimosaContext..IndexLocation table, e.g.,
<Root> <IndexState>0</IndexState> <CurrentChunkSize>786</CurrentChunkSize> <Asset AssetId="1001"> <IndexStateUpdateTS>2011-12-16 18:19:50</IndexStateUpdateTS> <StartMessageItemId>1002</StartMessageItemId> <EndMessageItemId>34725997</EndMessageItemId> <StartRecordTS>2011-12-16 18:20:02</StartRecordTS> <EndRecordTS>2012-05-17 08:04:21</EndRecordTS> <StartReceivedDate>2011-03-08 16:51:39</StartReceivedDate> <EndReceivedDate>2012-05-17 07:28:59</EndReceivedDate> </Asset> </Root>
To sort by the current chunk size, you have to use the XQUERY notation highlighted below:
SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK) WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID AND LV.LogicalVolumeID=IL.LogicalVolumeID AND LV.SharePath LIKE '%INDEX1%' ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC
For more information about working with XML in SQL, check out the following article on SQL Server Performance:
Practical Introduction to XML Manipulation Using XQuery in SQL Server
The following SQL query will show the current database server settings for the NearPoint databases:
select * from MimosaContext..DBMap
The data and log file paths are stored in xml format in the DBInfo field.
While helping a customer resolve an issue with their Nearpoint eDiscovery installation this morning, I learned that Autonomy recommends against configuring NearPoint to use same-named SQL logins and domain accounts. Apparently this can somehow confuse NearPoint when it tries to apply certain updates to the SQL databases. Autonomy recommends either using the SQL ‘sa’ account (always a bad idea) or creating/renaming the existing same-named SQL login account so that it no loner matches the NearPoint service account (the domain account). After making this change, you also need to run the dbreconfig.ps1
PowerShell script to update the DB user accounts on the master node. Prior to running that script, you also need to explicitly grant the SQL login dbo rights to the NearPoint databases under the User Mapping section of the login properties.
The following is a quote from an Autonomy support engineer regarding this issue:
Not all Stored Procedures (SPs) that NearPoint runs need to connect using SQL Authentication, however NearPoint needs to connect to the databases and run certain SPs using SQL Authentication. From what I know, most of these are during upgrades and rollup installs. If the DBuser account is named the same as any NT account, NearPoint’s attempt to connect will default to Windows Authentication and will fail. There’s certain changes to the databases that need to be done as a db owner using SQL Authentication.
This could explain why you’ve had apparently so many problems with upgrades and rollup installs in the past.
The following code snippet shows how you can use the sp_testlinkedserver procedure to determine whether a linked server is available:
BEGIN TRY EXEC sp_testlinkedserver N'linked_server_name'; END TRY BEGIN CATCH PRINT 'Linked Server not available'; ROLLBACK; RETURN; END CATCH -- continue with usage of linked server
See the following links for further discussion of this issue:
How do I prevent linked server errors?
Test linked server connection settings…
If you have a string containing the name of a SQL server, here’s a simple script to derive the Windows computer name from the instance name using SUBSTRING
:
DECLARE @SQLInstance NVARCHAR(128) DECLARE @SQLMachineName NVARCHAR(128) SET @SQLInstance = @@SERVERNAME -- Derive @SQLMachineName from @SQLInstance IF CHARINDEX('', @SQLInstance) = 0 BEGIN SELECT @SQLMachineName = @SQLInstance END ELSE BEGIN SELECT @SQLMachineName = SUBSTRING(@SQLInstance, 1, CHARINDEX('', @SQLInstance)-1) END
References:
Marcos Crispino posed the following question over at stackoverlow:
I have a Transact-SQL query that uses the IN operator. Something like this:
select * from myTable where myColumn in (1,2,3,4)
Is there a way to define a variable to hold the entire list “(1,2,3,4)”? How should I define it?
declare @myList {data type} set @myList = (1,2,3,4) select * from myTable where myColumn in @myList
To which LukeHK posted the following brilliantly simple solution using a TABLE
variable:
DECLARE @MyList TABLE (Value INT) INSERT INTO @MyList VALUES (1) INSERT INTO @MyList VALUES (2) INSERT INTO @MyList VALUES (3) INSERT INTO @MyList VALUES (4) SELECT * FROM MyTable WHERE MyColumn IN (SELECT Value FROM @MyList)
References: