SQL Management Server & Query

[#1]

Hi Guys

I am under so much pressure from my SQL Server DBA, that I am not permitted to query the server until after hours, as the Memory Usage hits 99% when I query the DB.  Its a SQL SERVER with 24gb of RAM.  He says SQL Queries are holding memory and do not release the memory, and hence it eventually topples over and requires a re-boot to normalize it again.  The server run our MIS and has many users logged in, about 200 users at any one time.

I simply cannot believe that I cannot use Qlikview Incremental Load Queries every two hours duringt he day to provide up to date information to my Qlikview users; because of this problem.  Has anyone encourntered this ebfore, as I cannto believe SQL do not have a solution for releasing memory etc

Please help or point in right direction.

[vikasmahajan]

Dear Sir,

You can do one thing  you can ask your IT guy to schedule backup in some common folder and you can

restore this backup every 2 hrs automatically via sql server schedular as I have faced same scanrio

I did the same. and schedule BI after auto restore of sql backup.

Hope this will not have any performance issue at both end.

Vikas Mahajan

[jagan]

HI,

Can you post the queries you are executing?  So that it would be helpful in analyzing, that there is any problem in your queries.

Regards,

Jagan.

[#4]

Having spoken with my IT Director whom indicated it’s the MIS system, in that they designed it so that it could not be efficiently queried. They have forbidden us to query the DB, as they say they are the only ones whom should do so, and that Crystal Reports are the only way to extract the data!!

An example of the query would be as follows:

SELECT RL_DATE, RL_CUST, Sum(RL_EXCL) as RL_VALUE

FROM RL (NOLOCK)

WHERE RL_ERA <>’’ and RL_DATE between ‘2013-06-01’ and ‘2013-06-30’ and RL_TRAN_TYPE in (0,1) and RL_EXCL <> 0 and RL_JOB_NUM <>’’

Thanks for you help

[jagan]

HI,

Did you executed this query manually in the machine in which you are reloading the qlikview dashboards?  If not check whether this query is taking more time to execute.

If it is taking more time then try like this in dashboard

DataTemp:

SELECT RL_DATE, RL_CUST, RL_EXCL as RL_VALUE

FROM RL (NOLOCK)

WHERE RL_ERA <>’’ and

RL_DATE between ‘2013-06-01’

and ‘2013-06-30’ and

(RL_TRAN_TYPE = 0 OR RL_TRAN_TYPE = 1) and RL_EXCL <> 0 and RL_JOB_NUM <>’’

Now calculate sum in Qlikview using Load and group by clause

Data:

LOAD

  RL_DATE, RL_CUST,

  Sum(RL_VALUE)  AS RL_VALUE_TOTAL

RESIDENT DataTemp

GROUP BY RL_DATE, RL_CUST;

DROP TABLE DataTemp;

Hope this helps you.

Regards,

Jagan.