Tag Archives: SQL

Optimizing SQL Transaction Log Throughput

Posted by —

0

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

Monitoring NearPoint Index Movement using SQL

Posted by —

0

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

SQL Query to Identify Top 10 Largest NearPoint Mailbox Indices

Posted by —

0

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=&quot;1001&quot;>
    <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

NearPoint SQL Server Authentication vs. Windows Authentication

Posted by —

0

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.

Test to See if Linked Server is Available

Posted by —

0

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…

Simple Query to Derive Machine Name from SQL Instance Name

Posted by —

0

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:

SUBSTRING (Transact-SQL)

Parameterizing a SQL ‘IN’ Clause

Posted by —

0

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:

Define variable to use with IN operator (T-SQL)