sql server 2012

hi,
i am having one scheduled sp. if i shrink the database scheduled running fastly ( contain while loop  and 40 lak loans running one by one loans ) . otherwise running slowly. is this any thing we need to do?  i have created index and missing index also.
kowsika deviAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Difficult to answer....  Do you need help with the SP, or DB or your SQL Server performance ?

If the problem manifests itself in the SP, maybe that is a good place to start...

Can you share more details ? Like the SP ?
0
Pawan KumarDatabase ExpertCommented:
Loops are slow in nature. We should always use SET Based approach.

For more details pls attach execution plan. You may be having some cache..
1
Pushpakumara MahagamageVPCommented:
Have you schedule rebuild index, re organize index and Update statistic  as maintenance task.
Schedule txn log backup with relevant retention policy, such as every 15 min and that will backup log file and avoid creating huge LDF. Normal DBA is not schedule Database shrink.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
>>"Loops are slow in nature. We should always use SET Based approach."

Loops are slower than equivalent set based operatons, so there are good reasons to prefer use of "set based" approaches
(but, I don't think we can "always" use a set based approach)

>>"i have created index and missing index also."
without the benefit of seeing any code or tables or data we are literally "flying blind"
can you share the code and  the execution plan (.sqlplan file)?
1
ste5anSenior DeveloperCommented:
A mere shrink should not have that effect. But it sounds like you have a lot of deletions, thus high fragmentation and possible too few indices.

Thus:
- Check your tables for appropriate indices. This includes checking whether you use meaningful clustered indices.
- You should run index maintenance on a regular basis. Run the reorganize task and update statistics task in a maintenance plan daily at least.
- Run your SP with WITH RECOMPILE.
- Do you run a single, large import task on a regular basis? Then you should evaluate whether running  DBCC FREEPROCCACHE and/or DBCC DROPCLEANBUFFERS after that import has a positive effect.
1
kowsika deviAuthor Commented:
am attaching my execution plan jj pl check and tell me any changes need to do. my exe plan shows cost  25% am not known about the cost and all.how can i check ?
execution-plan1.sqlplan
executionplan2.sqlplan
08.Loln_spOdcAmtCalculation.sql
02.Table-structure-of-Tran-Process.sql
CDABOTW1404170006.xlsx
0
kowsika deviAuthor Commented:
i have created temp table like this
        IF Object_id('tempdb..#Lgen_sum') IS NOT NULL
        DROP TABLE #Lgen_sum

      CREATE TABLE #Lgen_sum
        (
           lnno         VARCHAR(20),
           Pdtshrtdescr VARCHAR(2),
           SettledDt    DATETIME,
           SeizureDt    DATETIME,
           SaleDt       DATETIME,
           ReleaseDt    DATETIME,
           commondt     DATETIME,
               EffDisbmtDt                  DATETIME,
               OdcCalcMthd  char(1),
               odcprcnt     NUMERIC(10, 2)  
        )


            

CREATE NONCLUSTERED INDEX #lgen_sumLnno
  ON #lgen_sum(LnNo)  
 
CREATE NONCLUSTERED INDEX #lgen_sumSzureDt
  ON #lgen_sum(SeizureDt)  include (ReleaseDt)

is this ok iam attaching execu plan.
temptableexecplan.sqlplan
0
kowsika deviAuthor Commented:
hi jj for inserting #lgen_sum this table only taking time.
how the cost value should be 100% or some less value. pl explain that too.
0
ste5anSenior DeveloperCommented:
Kowiska is right about indexing your temporary table. But the first step is to design it right. No table besides staging tables for data import can have all columns NULLable. This is also important for index/statistics usage.

What also should have a performance effect: using BEGIN/COMMIT TRANSACTION inside of your loop. Which is suspicious. Cause can your loop run in an error? Then you would have some committed data, but not all..
0
kowsika deviAuthor Commented:
how can i change my sp stefan jj
0
ste5anSenior DeveloperCommented:
You have a lot of table scans on #Lgen_sum. Here check first, which columns cannot be null. Declare them appropriately.

Then check for candidate keys. The initial insert:

CREATE TABLE #Lgen_sum
    (
        lnno VARCHAR(20) ,
        Pdtshrtdescr VARCHAR(2) ,
        SettledDt DATETIME ,
        SeizureDt DATETIME ,
        SaleDt DATETIME ,
        ReleaseDt DATETIME ,
        commondt DATETIME ,
        EffDisbmtDt DATETIME ,
        OdcCalcMthd CHAR(1) ,
        odcprcnt NUMERIC(10, 2)
    );

INSERT INTO #Lgen_sum ( lnno ,
                        Pdtshrtdescr ,
                        SettledDt ,
                        EffDisbmtDt ,
                        OdcCalcMthd ,
                        odcprcnt )
            SELECT lnno ,
                   Pdtshrtdescr ,
                   StlmntFirstAppvlRejDate ,
                   EffDisbmtDt ,
                   OdcCalcMthd ,
                   odcprcnt
            FROM   Loln_lndetails_h WITH ( NOLOCK )
            WHERE  lnno = @lnno;

Open in new window


Is lnno in Loln_lndetails_h unique? Then you should create a primary key on it. When not, then your index should be clustered

CREATE CLUSTERED INDEX #lgen_sumLnno
    ON #Lgen_sum ( lnno );

Open in new window


p.s. you know, that using NOLOCK can lead to wrong results?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kowsika deviAuthor Commented:
ya jj i created.

CREATE CLUSTERED INDEX #lgen_sumLnno
    ON #Lgen_sum ( lnno );

but i have one confusion #lgen_sum table contain any way only one no that also need to create index???
0
ste5anSenior DeveloperCommented:
When there is only one row in it, why then using a temp table instead of a table variable?

Just keep in mind: it's a large procedure without all involved tables DDL. So it's hard to see, why you are doing what for what reason.
0
kowsika deviAuthor Commented:
this sp purpose is to calculate over due charges. if customer paid month on month due there is no odc if not senario wise we will charge odc.
so that for a single lnno it may go 24 installments... installment wise customer will repay amount.
0
kowsika deviAuthor Commented:
iam scheduled my sp for 15 min only 500 loans runned....table contain ( distinct loans) 40 lak loans how can i make faster.
0
ste5anSenior DeveloperCommented:
Post the entire table DDL of the involved tables. The table DDL for Loln_lndetails_h and OdcNewLogic are missing.

btw, check you model. From your Excel data sample, your posted table creation script is incomplete..
Cause the first 11 columns seems to be NOT NULL.

Just for curiosity, how many disks to you use in your server distributed over what volumes? How much RAM and CPU?
0
kowsika deviAuthor Commented:
i dono about RAM and CPU used jj...how can i check that ....i have another doubt iam copied my db from live server last week after that i executed my DDL 's and my SPs for checking is that need to rebuild my index again???
oa any update statistics i need to do
0
kowsika deviAuthor Commented:
After Rebuild my index for all table and ur idea to create temp table index  and some log file shrinked running falstly jj.  thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.