C# Driver for Apache Cassandra Remote Authentication

Apache Cassandra is an open source distributed database management system designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure.

They have many APIs and Drivers available at the following URL http://www.datastax.com/download/clientdrivers 

One of those drivers is Cassandra-sharp , the philosophy of cassandra-sharp is to be really simple and fast: no Linq provider, no complex API. Just CQL, simple object mapping and great performance.

One of the requirements I had for our cloud environment was being able to create user accounts and assign privileges on KEYSPACES.

I came up with a simple class and everything worked just fine on my local machine, however whenever we attempted to do remote authentication we got this error message:

_cluster= Cluster.Builder().WithCredentials("user","pass").WithPort(port).AddContactPoint(node).Build();

Cassandra.AuthenticationException: Unsupported Authenticator org.apache.cassandra.auth.PasswordAuthenticator

We opened a case with DATASTAX and they mentioned the C# modified version for DSE 3.1/3.2 authentication wasn't available yet but might be ready by the end of January 2014.

I'll will share with you once the updated driver is provided if this solves the Remote Authentication issue.

UPDATE: This has been fixed with the latest version of Cassandra C# driver.

Statistics IO Parser

One of the best ways to start tuning your queries is by looking at the amount of disk activity generated by your Transact-SQL statements.

The way you do this is by setting up STATISTICS IO ON at run time like this.

SET STATISTICS IO ON

The output will display the following information.

The output of the following query would look something like:

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PurchaseOrderHeader'. Scan count 1, logical reads 44, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO ON

select * from [Purchasing].[PurchaseOrderHeader] a

join [Purchasing].[PurchaseOrderDetail] b

on a.PurchaseOrderID = b.PurchaseOrderID

where OrderDate >= '2004-05-17 00:00:00.000'

This is cool but what if you want to see the Total I/O generated by the query and see the output better formatted?  Today I found this website built by Richie Rump http://statisticsioparser.com/ which formats the output of the IO Statistics.

Just enter the output you got from turning STATISTICS IO ON and click on Parse button

You will get a nice formatted output including the IO Totals

Memory Usage per Database

When we run a query the data is read from disk and placed into a memory area in SQL Server called buffer cache (also known as buffer pool). The buffer cache allow us to read this data subsequently without having to go to disk again.

Since SQL Server 2005 on wards with the introduction of DMVs (Dynamic Management Views) it is possible to get how much memory each database is taking in the buffer cache.

With SSRS, Crystal Reports or your favorite coding language you could create a Chart that will help you identify which memory is taking more space in the buffer cache.

I developed my own dashboard which allows me to check several performance checks at once! Comment here if you use a similar approach and what performance checks you use in your charts.

---Memory used per database in buffer pool
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

ORDER BY DatabaseName

Database I/O

Database I/O is the transfer rate, in megabytes per second, of data from memory to disk, disk to memory, or disk to disk.

The easiest way to check the I/O (MB/sec) on your SQL Instance is by checking SSMS Activity Monitor 

b) You can correlate this information and look at the Server Wait types . You will probably get wait types that are related to I/O (PAGEIOLATCH_IO, ASYNC_DISKPOOL_LOCK, IO_COMPLETION, ASYNC_IO_COMPLETION) 

High I/O causes are normally due outdated statistics, bad query design, lack of proper indexing and/or implicit transactions.

Identify your server's waits

Identifying the cause of your server’s waits is a great starting point for analyzing, at a
high level, why your queries are waiting and thus giving suboptimal performance.

Running the SQL script shown here will identify the top 10 causes of waiting on your server instance.

Use SSRS/Crystal Reports or .NET to create a nice dashboard showing this data in a friendly manner this will help you visualize the top offender(s) wait type(s).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH Waits AS

    (SELECT TOP 10

        wait_type,

        wait_time_ms / 1000.0 AS WaitS,

        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

        signal_wait_time_ms / 1000.0 AS SignalS,

        waiting_tasks_count AS WaitCount,

        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN (

        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',

        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',

        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')

    )

SELECT TOP 10

    W1.wait_type AS WaitType,  

    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

FROM Waits AS W1

    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 95;

Database on Suspect Mode

NOTE: The below was a very particular scenario,  the best way to recover a SUSPECT Database is to restore from a BACKUP.

Scenario

After a network issue with the SAN one of our databases went to suspect mode. Checking the logs both Windows App Logs and SQL Server Error Logs we got this:

LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.

The log for database 'X' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

During undoing of a logged operation in database 'X', an error occurred at log record ID (326627:109731:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

First we verified the Log Drive was present and the Database Log file was there (it was) so this was good.

We try to bring the database online since the log file was there and there was no need to rebuild it.

ALTER DATABASE [x] SET ONLINE

But we hit this error.

Msg 5061, Level 16, State 1, Line 1

ALTER DATABASE failed because a lock could not be placed on database 'TQ_ThreatLinQ'. Try again later.

Verified there were no process running against this database but just in case I try to put it in RESTRICTED_MODE

ALTER DATABASE [x] SET RESTRICTED_USER NO_WAIT

Same error, the DB STARTUP background process was blocking any attempt against this database, however it was not doing a thing.

Since this Database was part of a dedicated cluster we fail over the DB Instance and that fix the issue.

Recovery went successfully this time for the database.

Recovery is complete. This is an informational message only. No user action is required.

Databases with most missing Indexes

It’s often the case that you have several different databases running on the same server. A consequence of this is that no matter how optimal your individual database may be, another database on the server, running suboptimally, may affect the server’s resources, and this may impact the performance of your database. Remember, CPU, memory, and tempdb are shared across all the databases on the server. Now that you know about the importance of indexes on query performance, it makes sense to report on those databases with the most missing indexes, because they may be indirectly affecting the performance of your database

High I/O is one of the main reasons for poor performance and often this is caused by missing indexes. The following query will list the databases with most missing indexes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

DB_NAME(database_id) AS DatabaseName

, COUNT(*) AS [Missing Index Count]

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY [Missing Index Count] DESC