Dropping in SQL

After a little sojourn in to the world of creating new indices, I then had to clear up a little bit. Here is how to delete an index :

Drop Index

You can delete an existing index in a table with the DROP INDEX statement.

Syntax for Microsoft SQLJet (and Microsoft Access):

DROP INDEX index_name ON table_name

Syntax for MS SQL Server:

DROP INDEX table_name.index_name

Syntax for IBM DB2 and Oracle:

DROP INDEX index_name

Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

Seems that various methods work at least on Microsoft SQL 2005, for example this worked for me :

drop INDEX [IX_Vault_Items] on vault

Note: idea taken from http://www.w3schools.com/SQL/sql_drop.asp

Marvels of SQL Tuning

Okay so I am a TOTAL novice at doing this. Well, even worse than that. I know even less than a novice that has only just arrived at the first class to discover that he’s actually in a lecture about quantum physics.

But, I never give up with trying to learn stuff, and this is very interesting!

First of all there is a MOUNTAIN of stuff on the internet, some of it useful, some of it lacks context, some of it is repetitive (and I’m adding to that last category here I suppose). My experience though is worth sharing, because it’s from the novices perspective.

I have a bit of SQL that runs slowly. It seems all a-okay when there is only a handful of records in the table, but when ths size of the table grows, the performance falls through the floor.

This is the query :

SELECT
COUNT(*) as VaultCount,
SUM(ArchivedItems) as ArchivedItems,
SUM(CAST(ArchivedItemsSize as decimal(20, 0))) as ArchivedItemsSize
From VAULT

When this runs on a Vault table with millions of rows, then it takes a LONG time to run. What I did was take the query, and run it a number of times — 5 times in fact — then work out the average run time. If you run the query with the option to show the execution plan, you can see which bits of the query take the longest time… I didn’t really understand the output though to be fair, so I won’t go in to the details. The bit I did understand was the the index being used wasn’t efficient it seems.

The SQL 2005 SP 2 tuning wizard suggested the following index to add :

CREATE NONCLUSTERED INDEX
[_dta_index_Vault_6_165575628__K4_5] ON [dbo].[Vault]
(
[ArchivedItems] ASC
)
INCLUDE ( [ArchivedItemsSize]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY =
OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

After adding that index, I run the same query a number of times — 5 times in fact — this time the average run time is 20 seconds.

Result !

Altering text output in SQL Select Statements

Something I learnt today, which surprised me… When you do a select statement and it includes a wide text column SQL Query Analyzer truncates that by default to 256 characters.

To get around this go in to SQL Query Analyzer, then go to Tools -> Option, and click on the Results tab. You will see an entry saying :

Maximum characters per column: 256

Change that value, and hey presto it will show you a much wider column.

SQL Reporting Services

Installing SQL 2000 RS

When you run the installation you get a pop-up warning about not having Visual Studio 2003 installed, this only affects Report Designer, and we can live without that.  You do need to make sure that SQL 2000 SP 4 is installed, or you get most of the way through, and then can’t choose the default Credentials Type – or rather the default is Service Account, but you can’t opt for that (http://support.microsoft.com/?id=821334)

When asked about which account you want the ReportServer Windows service to run under, I left this at NT Authority\Network Service, and I left the service at Auto-start.

For the Virtual Directory information I had the following defaults configured :

Report Server Virtual Directory: ReportServer

Report Manager Virtual Directory: Reports

Use SSL: This was ticked, but I unticked it, because I don’t have SSL configured.

Next you are asked about the Report Server Database :

SQL Server Instance: Evault1

Name: ReportServer

Credentials Type: Service Account

I left all those as-is.

There were 1 or 2 other items to fill in, but they are straight forward.  Installation then runs though.

EV 2007 Installing and Configuring Guide says :

Microsoft SQL Server 2000 Reporting Services with SP 2, or Microsoft SQL Server 2005 Reporting Services (SP1 recommended)

Troubleshooting article for EV Reporting :

http://atlas.veritas.com/avf/aca-1/dispatch.exe/mynotes/lookup/285815/0

Configuration

Need to try and make sure the installation fails, so let’s try configuring “My Reports” first of all.

Go to http://evault1/Reports, and login.  This is the first screen :

I now want to turn on “My Reports”, so that I can get the EV Reports installation to fail, to do that click on Site Settings, to go to :

On that screen tick the box for enabling the reports, and click Apply.

Installing SQL 2000 Reporting Services SP 2

This is just a small (20 Mb) executable to run.  It is listed as a pre-req for EV Reports so rather than suffer any problems, or a failed installation because of that, I installed that.

Installing EV Reports

Looks like you need to run evdeployreports.exe, however, I can’t find that !  So, to get the first run through I did :

* Stopped all EV Services

* Stopped IIS Admin Service

Then re-run the setup.exe from the EV installation CD.  This then gives the option (as a tick box) so that you can install reports.

Running for the first time

After restarting the IIS services, and EV, you now have an extra menu item called “Enterprise Vault Reports Configuration”.  You also have an EVReports folder underneath the main Enterprise Vault folder on the file system.  Clicking on that option in the start menu, brings up this screen :

I used the Vault Service account on this screen, though I really suspect that I should use something else.  I can check that in the Installation Guide.

After a few minutes, and a bit of IIS service restart action, I got a failure message :

And the log file (which is in the EVReports folder says) :

W3SVC is currently stopped so starting it
W3SVC is currently waiting to start
W3SVC started successfully
Leaving UIHelper::StartService
Leaving UIHelper::StartChild
Entered UIHelper::StartChild
Entered UIHelper::StartChild
Starting service :W3SVC
Entered UIHelper::StartService
W3SVC started successfully
Leaving UIHelper::StartService
Leaving UIHelper::StartChild
Starting service :HTTPFilter
Entered UIHelper::StartService
HTTPFilter started successfully
Leaving UIHelper::StartService
Leaving UIHelper::StartChild
Starting service :IISADMIN
Entered UIHelper::StartService
IISADMIN started successfully
Leaving UIHelper::StartService
Leaving UIHelper::StartChild
Leaving UIHelper::RestartIIS
Restarting IIS successful
Entered Configuration::CreateRootFolder()
Entered RsHelper::ListReportServerItems()
System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: The value for parameter ‘Item’ is not specified. It is either missing from the function call, or it is set to null. —> Microsoft.ReportingServices.Diagnostics.Utilities.MissingParameterException: The value for parameter ‘Item’ is not specified. It is either missing from the function call, or it is set to null.
at Microsoft.ReportingServices.WebServer.ReportingService.GetProperties(String Item, Property[] Properties, Property[]& Values)
— End of inner exception stack trace —
at Microsoft.ReportingServices.WebServer.ReportingService.GetProperties(String Item, Property[] Properties, Property[]& Values)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ReportingService.GetProperties(String Item, Property[] Properties)
at Symantec.EnterpriseVault.Reporting.RsHelper.ListReportServerItems(String folderPath, Boolean recursive)
at Symantec.EnterpriseVault.Reporting.Configuration.CreateRootFolder()
at Symantec.EnterpriseVault.Reporting.Configuration.Install()
Symantec.EnterpriseVault.Reporting.DeploymentException: Error code: SoapError
at Symantec.EnterpriseVault.Reporting.Configuration.Install()
at Symantec.EnterpriseVault.Reporting.AppClass.Install(Configuration cnfg)
at Symantec.EnterpriseVault.Reporting.AppClass.ConfigureReporting()
Entered ReportDeployForm::DeployReportsCallback
Entered Configuration::Configuration()
Entered RsHelper::RsHelper()
Leaving RsHelper::RsHelper()
Leaving Configuration::Configuration()
Entered RsHelper::ListReportServerItems()
Symantec.EnterpriseVault.Reporting.DeploymentException: Error code: SoapError

Server stack trace:
at Symantec.EnterpriseVault.Reporting.AppClass.ConfigureReporting()
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at Symantec.EnterpriseVault.Reporting.ReportDeployForm.DeployReportsDelegate.EndInvoke(IAsyncResult result)
at Symantec.EnterpriseVault.Reporting.ReportDeployForm.DeployReportsCallback(IAsyncResult asyncResult)
Leaving ReportDeployForm::DeployReportsCallback

Fixing it Up

Go back to the SQL Reporting Site Settings page, and turn off My Reports.  Re-run the EVDeployReports.exe