4 Replies Latest reply: Jul 30, 2013 7:31 AM by jagan mohan rao appala RSS

    SQL Management Server & Query

    Bruce Tedder

      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.

        • Re: SQL Management Server & Query
          vikas mahajan

          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

          • Re: SQL Management Server & Query
            jagan mohan rao appala

            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.

              • Re: SQL Management Server & Query
                Bruce Tedder

                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

                  • Re: SQL Management Server & Query
                    jagan mohan rao appala

                    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.